<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  
  <title>Hexo</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  <meta name="description">
<meta property="og:type" content="website">
<meta property="og:title" content="Hexo">
<meta property="og:url" content="http://yoursite.com/page/2/index.html">
<meta property="og:site_name" content="Hexo">
<meta property="og:description">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="Hexo">
<meta name="twitter:description">
  
    <link rel="alternative" href="/atom.xml" title="Hexo" type="application/atom+xml">
  
  
    <link rel="icon" href="/favicon.png">
  
  <link href="//fonts.googleapis.com/css?family=Source+Code+Pro" rel="stylesheet" type="text/css">
  <link rel="stylesheet" href="/css/style.css" type="text/css">
  

</head>
<body>
  <div id="container">
    <div id="wrap">
      <header id="header">
  <div id="banner"></div>
  <div id="header-outer" class="outer">
    <div id="header-title" class="inner">
      <h1 id="logo-wrap">
        <a href="/" id="logo">Hexo</a>
      </h1>
      
    </div>
    <div id="header-inner" class="inner">
      <nav id="main-nav">
        <a id="main-nav-toggle" class="nav-icon"></a>
        
          <a class="main-nav-link" href="/">Home</a>
        
          <a class="main-nav-link" href="/archives">Archives</a>
        
      </nav>
      <nav id="sub-nav">
        
          <a id="nav-rss-link" class="nav-icon" href="/atom.xml" title="RSS Feed"></a>
        
        <a id="nav-search-btn" class="nav-icon" title="Search"></a>
      </nav>
      <div id="search-form-wrap">
        <form action="//google.com/search" method="get" accept-charset="UTF-8" class="search-form"><input type="search" name="q" results="0" class="search-form-input" placeholder="Search"><button type="submit" class="search-form-submit">&#xF002;</button><input type="hidden" name="sitesearch" value="http://yoursite.com"></form>
      </div>
    </div>
  </div>
</header>
      <div class="outer">
        <section id="main">
  
    <article id="post-database/9.oracle学习" class="article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    <a href="/2016/01/27/database/9.oracle学习/" class="article-date">
  <time datetime="2016-01-27T07:11:00.000Z" itemprop="datePublished">2016-01-27</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/categories/database/">database</a>
  </div>

  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 itemprop="name">
      <a class="article-title" href="/2016/01/27/database/9.oracle学习/">oracle学习</a>
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <h2 id="u7269_u5316_u89C6_u56FE"><a href="#u7269_u5316_u89C6_u56FE" class="headerlink" title="物化视图"></a>物化视图</h2><p>　　物化视图是存储数据的视图，预先计算并保存表连接或聚集等耗时较多的操作的结果，存储了基础表的全部或者一部分数据，主要用作sql语句的优化，查询物化视图比查询表中的数据速度要快。<br>　　物化视图有很多方面和索引很相似：使用物化视图的目的是为了提高查询性能；物化视图对应用透明，增加和删除物化视图不会影响应用程序中SQL语句的正确性和有效性；实体化视图需要占用存储空间；当基表发生变化时，实体化视图也应当刷新（自动或手动）。<br>　　当对物化视图的基表进行查询时，Oracle会自动判断能否通过查询物化视图来得到结果（查询重写），完全对应用透明。这样避免了聚集或连接操作，而直接从已经计算好的实体化视图中读取数据，默认 DISABLE QUERY REWRITE<br>　　物化视图可以直接update，但是不影响base table，对视图的update会反映到base table上; 创建语法为：CREATE MATERIALIZED VIEW</p>
<h2 id="EXISTS"><a href="#EXISTS" class="headerlink" title="EXISTS"></a>EXISTS</h2><p>　　EXISTS(包括 NOT EXISTS )内部有一个子查询语句(SELECT … FROM…)，它返回一个结果集。EXISTS子句根据<strong>其返回的结果集空或者非空，返回一个布尔值</strong>。一种通俗的可以理解为：将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果。<br>　　EXISTS与IN的使用效率的问题，通常情况下采用exists要比in效率高，因为<strong>IN不走索引</strong>，但要看实际情况具体使用：<br>IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。<br><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- aa，bb表都有20个字段，且记录数量都很大，aa，bb表的X字段（非空）上有索引</span></span><br><span class="line"><span class="comment">-- 列出aa表里面存在的X在bb表不存在的X的值，最优sql</span></span><br><span class="line"><span class="operator"><span class="keyword">select</span> aa.x <span class="keyword">from</span> aa <span class="keyword">where</span> <span class="keyword">not</span> <span class="keyword">exists</span> (<span class="keyword">select</span> <span class="string">'x'</span> <span class="keyword">from</span> bb <span class="keyword">where</span> aa.x = bb.x) ;</span></span><br><span class="line"><span class="comment">-- 以上语句同时使用到了aa中x的索引和的bb中x的索引</span></span><br></pre></td></tr></table></figure></p>
<p>##Having<br>　　Having对由sum或其他集合函数运算结果的输出进行限制，通常情况下，HAVING从句被放置在SQL命令的结尾处。<br><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--找出总额大于1500的销量</span></span><br><span class="line"><span class="operator"><span class="keyword">SELECT</span> store_name, <span class="keyword">SUM</span>(sales)  <span class="keyword">FROM</span> Store_Information <span class="keyword">GROUP</span> <span class="keyword">BY</span> store_name <span class="keyword">HAVING</span> <span class="keyword">SUM</span>(sales) &gt; <span class="number">1500</span></span></span><br></pre></td></tr></table></figure></p>
<h2 id="case_when_then_end"><a href="#case_when_then_end" class="headerlink" title="case when then end"></a>case when then end</h2><figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br></pre></td><td class="code"><pre><span class="line">-- 员工表empinfo </span><br><span class="line">( </span><br><span class="line">Fempno varchar2(10) not null pk, </span><br><span class="line">Fempname varchar2(20) not null, </span><br><span class="line">Fage number not null, </span><br><span class="line">Fsalary number not null </span><br><span class="line">); </span><br><span class="line">-- 假如数据量很大约1000万条；用最高效的SQL计算以下四种人： </span><br><span class="line">fsalary&gt;9999 and fage &gt; 35 </span><br><span class="line">fsalary&gt;9999 and fage &lt; 35 </span><br><span class="line">fsalary &lt;9999 and fage &gt; 35 </span><br><span class="line">fsalary &lt;9999 and fage &lt; 35 </span><br><span class="line">-- 每种员工的数量； </span><br><span class="line">select </span><br><span class="line">    sum(case when fsalary &gt; 9999 and fage &gt; 35</span><br><span class="line">        then 1 else 0 end) as "fsalary&gt;9999_fage&gt;35",</span><br><span class="line">    sum(case when fsalary &gt; 9999 and fage &lt; 35</span><br><span class="line">        then 1 else 0 end) as "fsalary&gt;9999_fage&lt;35",</span><br><span class="line">    sum(case when fsalary &lt; 9999 and fage &gt; 35</span><br><span class="line">        then 1 else 0 end) as "fsalary&lt;9999_fage&gt;35",</span><br><span class="line">    sum(case when fsalary &lt; 9999 and fage &lt; 35</span><br><span class="line">        then 1 else 0 end) as "fsalary&lt;9999_fage&lt;35"</span><br><span class="line">from empinfo;</span><br></pre></td></tr></table></figure>
<h2 id="u4F4D_u56FE_u7D22_u5F15"><a href="#u4F4D_u56FE_u7D22_u5F15" class="headerlink" title="位图索引"></a>位图索引</h2><p>　　位图索引主要针对大量相同值的列而创建(例如：类别，操作员，部门ID,库房ID等)</p>
<h2 id="u5343_u4E07_u7EA7_u6570_u636E_u63D2_u5165"><a href="#u5343_u4E07_u7EA7_u6570_u636E_u63D2_u5165" class="headerlink" title="千万级数据插入"></a>千万级数据插入</h2><p>　　1. 没插入一条数据，提交<br>　　2. 批量插入，一次性提交<br>　　3. nologging(不产生日志)<br><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="operator"><span class="keyword">alter</span> <span class="keyword">table</span> table_name1 nologging</span><br><span class="line"><span class="keyword">insert</span> <span class="comment">/*+ append */</span> <span class="keyword">into</span> table_name1 <span class="keyword">select</span> * <span class="keyword">from</span> table_name2</span></span><br></pre></td></tr></table></figure></p>
<p>　　4. parallel<br><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="operator"><span class="keyword">alter</span> <span class="keyword">session</span> <span class="keyword">enable</span> <span class="keyword">parallel</span> dml;</span></span><br><span class="line"><span class="operator"><span class="keyword">insert</span> <span class="comment">/*+parallel(A1,8)*/</span>  <span class="comment">/*+ APPEND */</span> <span class="keyword">into</span> A1  nologging <span class="keyword">select</span> <span class="comment">/*+parallel(A2,10)*/</span> * <span class="keyword">from</span> A2 ;</span></span><br></pre></td></tr></table></figure></p>
<p>　　5. 关索引</p>
<h2 id="DBLink"><a href="#DBLink" class="headerlink" title="DBLink"></a>DBLink</h2><h2 id="rollup"><a href="#rollup" class="headerlink" title="rollup"></a>rollup</h2><h2 id="rman"><a href="#rman" class="headerlink" title="rman"></a>rman</h2><h2 id="u4E3B_u952E_u81EA_u589E"><a href="#u4E3B_u952E_u81EA_u589E" class="headerlink" title="主键自增"></a>主键自增</h2><p>　　在oracle中，主键自动递增需要sequence和触发器配合使用。sequence就是序号，每次取的时候它会自动增加，注意：<strong>sequence与表没有关系</strong>。</p>
<h4 id="Create_Sequence"><a href="#Create_Sequence" class="headerlink" title="Create Sequence"></a>Create Sequence</h4><p>　　首先要有CREATE SEQUENCE或者CREATE ANY SEQUENCE权限。创建语句如下：<br><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="operator"><span class="keyword">CREATE</span> <span class="keyword">SEQUENCE</span> seqTest</span><br><span class="line">  <span class="keyword">INCREMENT</span> <span class="keyword">BY</span> <span class="number">1</span> <span class="comment">-- 每次加几个</span></span><br><span class="line">  <span class="keyword">START</span> <span class="keyword">WITH</span> <span class="number">1</span> <span class="comment">-- 从1开始计数</span></span><br><span class="line">  <span class="keyword">NOMAXvalue</span> <span class="comment">-- 不设置最大值</span></span><br><span class="line">  <span class="keyword">NOCYCLE</span> <span class="comment">-- 一直累加，不循环</span></span><br><span class="line">  <span class="keyword">CACHE</span> <span class="number">10</span>;</span> <span class="comment">--设置缓存cache个序列，如果系统down掉了或者其它情况将会导致序列不连续，也可以设置为NOCACHE</span></span><br></pre></td></tr></table></figure></p>
<h4 id="u83B7_u53D6Sequence_u503C"><a href="#u83B7_u53D6Sequence_u503C" class="headerlink" title="获取Sequence值"></a>获取Sequence值</h4><p>　　定义好sequence后，你就可以用currVal，nextVal取得值。CurrVal：返回sequence的当前值。NextVal：增加sequence的值再返回，语法为：SELECT Sequence名称.CurrVal FROM DUAL;<br><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 自增长的字段</span></span><br><span class="line"><span class="operator"><span class="keyword">insert</span> <span class="keyword">into</span> 表名(<span class="keyword">id</span>,<span class="keyword">name</span>)<span class="keyword">values</span>(seqtest.<span class="keyword">Nextval</span>,<span class="string">'sequence 插入测试'</span>);</span></span><br></pre></td></tr></table></figure></p>
<p>　　注意：<br>　　1. 第一次NEXTVAL返回的是初始值，随后的NEXTVAL会自动增加定义的INCREMENT BY值，然后返回增加后的值。<br>　　2. CURRVAL在第一次NEXTVAL初始化之后才能使用CURRVAL，否则会出错。<br>　　3. 一次NEXTVAL会增加一次SEQUENCE的值，所以在同一个语句里面使用多个NEXTVAL，其值就是不一样的。<br>　　4. 如果指定CACHE值，ORACLE就可以预先在内存里面放置一些sequence，这样存取的快些。cache里面的取完后，oracle自动再取一组到cache。使用cache或许会跳号，比如数据库突然不正常down掉（shutdown abort)，cache中的sequence就会丢失。所以可以在create sequence的时候用nocache防止这种情况。</p>
<h4 id="Alter_Sequence"><a href="#Alter_Sequence" class="headerlink" title="Alter Sequence"></a>Alter Sequence</h4><p>　　拥有ALTER ANY SEQUENCE权限才能改动sequence，可以alter除start值以外的所有sequence参数，如果想要改变start值，必须drop sequence再re-create。<br><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="operator"><span class="keyword">alter</span> <span class="keyword">sequence</span> SEQTEST maxvalue <span class="number">9999999</span>;</span></span><br><span class="line"><span class="operator"><span class="keyword">DROP</span> <span class="keyword">SEQUENCE</span> seqTest;</span></span><br></pre></td></tr></table></figure></p>
<h4 id="u793A_u4F8B"><a href="#u793A_u4F8B" class="headerlink" title="示例"></a>示例</h4><p>　　通过触发器和sequence实现主键自增<br><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line"><span class="operator"><span class="keyword">create</span> <span class="keyword">sequence</span> SEQ_ID</span><br><span class="line">  <span class="keyword">minvalue</span> <span class="number">1</span></span><br><span class="line">  maxvalue <span class="number">99999999</span></span><br><span class="line">  <span class="keyword">start</span> <span class="keyword">with</span> <span class="number">1</span></span><br><span class="line">  <span class="keyword">increment</span> <span class="keyword">by</span> <span class="number">1</span></span><br><span class="line">  nocache</span><br><span class="line">  <span class="keyword">order</span>;</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 创建触发器</span></span><br><span class="line"><span class="operator"><span class="keyword">create</span> <span class="keyword">or</span> <span class="keyword">replace</span> <span class="keyword">trigger</span> tri_test_id</span><br><span class="line">  <span class="keyword">before</span> <span class="keyword">insert</span> <span class="keyword">on</span> S_Depart   <span class="comment">--S_Depart 是表名</span></span><br><span class="line">  <span class="keyword">for</span> <span class="keyword">each</span> <span class="keyword">row</span></span><br><span class="line"><span class="keyword">declare</span></span><br><span class="line">  nextid <span class="built_in">number</span>;</span></span><br><span class="line"><span class="operator"><span class="keyword">begin</span></span><br><span class="line">  <span class="keyword">IF</span> :<span class="keyword">new</span>.DepartId <span class="keyword">IS</span> <span class="literal">NULL</span> <span class="keyword">or</span> :<span class="keyword">new</span>.DepartId=<span class="number">0</span> <span class="keyword">THEN</span> <span class="comment">--DepartId是列名</span></span><br><span class="line">    <span class="keyword">select</span> SEQ_ID.<span class="keyword">nextval</span> <span class="comment">--SEQ_ID正是刚才创建的</span></span><br><span class="line">    <span class="keyword">into</span> nextid</span><br><span class="line">    <span class="keyword">from</span> <span class="keyword">sys</span>.dual;</span></span><br><span class="line">    :new.DepartId:=nextid;</span><br><span class="line">  <span class="operator"><span class="keyword">end</span> <span class="keyword">if</span>;</span></span><br><span class="line"><span class="operator"><span class="keyword">end</span> tri_test_id;</span></span><br></pre></td></tr></table></figure></p>
<p>　　:new 代表数据改变后的新值，相对应的有:old原值<br>　　:= 代表赋值<br>　　:nextid表示引用sqlplus中定义的变量</p>
<h2 id="u4E24_u5217_u4E92_u6362"><a href="#u4E24_u5217_u4E92_u6362" class="headerlink" title="两列互换"></a>两列互换</h2><p>　　sql执行正确：update atest set name1 = name2, name2=name1<br>　　分析：在手动提交事物的情况下，name2的值给name1，由于事物未提交，此时name1原值仍然存在（此时name1的新值未冲掉原值）</p>
<h2 id="u6811_u67E5_u8BE2"><a href="#u6811_u67E5_u8BE2" class="headerlink" title="树查询"></a>树查询</h2><p>　　语法：select … from tablename start with 条件1 connect by 条件2 where 条件3;<br>　　示例：select * from table start with org_id = ‘HBHqfWGWPy’ connect by prior org_id = parent_id;<br>　　条件1：根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。<br>　　条件2：连接条件，其中用PRIOR表示上一条记录，比如 CONNECT BY PRIOR org_id = parent_id就是说上一条记录的org_id 是本条记录的parent_id，即本记录的父亲是上一条记录。<br>　　条件3：过滤条件，用于对返回的所有记录进行过滤。<br>　　扫描树结构表时，需要依此访问树结构的每个节点，一个节点只能访问一次，其访问步骤为：1. 从根节点开始；2.访问该节点；3. 判断该节点有无未被访问的子节点，若有，则转向它最左侧的未被访问的子节，并执行第二步，否则执行第四步；4. 若该节点为根节点，则访问完毕，否则执行第五步；5. 返回到该节点的父节点，并执行第三步骤。<br>　　总之：扫描整个树结构的过程也即是中序遍历树的过程。<br>　　PRIORY运算符必须放置在连接关系的两列中某一个的前面。对于节点间的父子关系，PRIOR运算符在一侧表示父节点，在另一侧表示子节点，从而确定查找树结构是的顺序是自顶向下还是自底向上。在连接关系中，除了可以使用列名外，还允许使用列表达式。START WITH子句为可选项，用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点。<br>    START WITH：不但可以指定一个根节点，还可以指定多个根节点。</p>
<h4 id="u5173_u4E8EPRIOR"><a href="#u5173_u4E8EPRIOR" class="headerlink" title="关于PRIOR"></a>关于PRIOR</h4><p>　　PRIOR被放置于等号前后的位置，决定着查询时的检索顺序。PRIOR被置于CONNECT BY子句中等号的前面时，则强制从根节点到叶节点的顺序检索，即由父节点向子节点方向通过树结构，我们称之为自顶向下的方式。如：CONNECT BY PRIOR EMPNO=MGR<br>　　PIROR被置于CONNECT BY子句中等号的后面时，则强制从叶节点到根节点的顺序检索，即由子节点向父节点方向通过树结构，我们称之为自底向上的方式。例如：CONNECT BY EMPNO=PRIOR MGR。在这种方式中也应指定一个开始的节点。</p>
<h4 id="u5B9A_u4E49_u67E5_u627E_u8D77_u59CB_u8282_u70B9"><a href="#u5B9A_u4E49_u67E5_u627E_u8D77_u59CB_u8282_u70B9" class="headerlink" title="定义查找起始节点"></a>定义查找起始节点</h4><p>　　在自顶向下查询树结构时，不但可以从根节点开始，还可以定义任何节点为起始节点，以此开始向下查找。这样查找的结果就是以该节点为开始的结构树的一枝。</p>
<h4 id="u4F7F_u7528LEVEL"><a href="#u4F7F_u7528LEVEL" class="headerlink" title="使用LEVEL"></a>使用LEVEL</h4><p>　　在具有树结构的表中，每一行数据都是树结构中的一个节点，由于节点所处的层次位置不同，所以每行记录都可以有一个层号。层号根据节点与根节点的距离确定。不论从哪个节点开始，该起始根节点的层号始终为1，根节点的子节点为2，依此类推。</p>
<h4 id="u8282_u70B9_u548C_u5206_u652F_u7684_u88C1_u526A"><a href="#u8282_u70B9_u548C_u5206_u652F_u7684_u88C1_u526A" class="headerlink" title="节点和分支的裁剪"></a>节点和分支的裁剪</h4><p>　　在对树结构进行查询时，可以去掉表中的某些行，也可以剪掉树中的一个分支，使用WHERE子句来限定树型结构中的单个节点，以去掉树中的单个节点，但它却不影响其后代节点（自顶向下检索时）或前辈节点（自底向顶检索时）。</p>
<h4 id="u6392_u5E8F_u663E_u793A"><a href="#u6392_u5E8F_u663E_u793A" class="headerlink" title="排序显示"></a>排序显示</h4><p>　　象在其它查询中一样，在树结构查询中也可以使用ORDER BY 子句，改变查询结果的显示顺序，而不必按照遍历树结构的顺序。<br><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 创建示例表：</span></span><br><span class="line"><span class="operator"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> TBL_TEST(</span><br><span class="line">  <span class="keyword">ID</span> <span class="built_in">NUMBER</span>,</span><br><span class="line">  <span class="keyword">NAME</span> VARCHAR2(<span class="number">100</span> <span class="keyword">BYTE</span>),</span><br><span class="line">  PID <span class="built_in">NUMBER</span> <span class="keyword">DEFAULT</span> <span class="number">0</span></span><br><span class="line">);</span></span><br><span class="line"><span class="comment">-- 插入测试数据：</span></span><br><span class="line"><span class="operator"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> TBL_TEST(<span class="keyword">ID</span>,<span class="keyword">NAME</span>,PID) <span class="keyword">VALUES</span>(<span class="string">'1'</span>,<span class="string">'10'</span>,<span class="string">'0'</span>);</span></span><br><span class="line"><span class="operator"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> TBL_TEST(<span class="keyword">ID</span>,<span class="keyword">NAME</span>,PID) <span class="keyword">VALUES</span>(<span class="string">'2'</span>,<span class="string">'11'</span>,<span class="string">'1'</span>);</span></span><br><span class="line"><span class="operator"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> TBL_TEST(<span class="keyword">ID</span>,<span class="keyword">NAME</span>,PID) <span class="keyword">VALUES</span>(<span class="string">'3'</span>,<span class="string">'20'</span>,<span class="string">'0'</span>);</span></span><br><span class="line"><span class="operator"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> TBL_TEST(<span class="keyword">ID</span>,<span class="keyword">NAME</span>,PID) <span class="keyword">VALUES</span>(<span class="string">'4'</span>,<span class="string">'12'</span>,<span class="string">'1'</span>);</span></span><br><span class="line"><span class="operator"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> TBL_TEST(<span class="keyword">ID</span>,<span class="keyword">NAME</span>,PID) <span class="keyword">VALUES</span>(<span class="string">'5'</span>,<span class="string">'121'</span>,<span class="string">'2'</span>);</span></span><br><span class="line"><span class="comment">-- 从Root往树末梢递归</span></span><br><span class="line"><span class="operator"><span class="keyword">select</span> * <span class="keyword">from</span> TBL_TEST <span class="keyword">start</span> <span class="keyword">with</span> <span class="keyword">id</span>=<span class="number">1</span> <span class="keyword">connect</span> <span class="keyword">by</span> <span class="keyword">prior</span> <span class="keyword">id</span> = pid</span><br><span class="line"><span class="comment">-- 从末梢往树ROOT递归</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> TBL_TEST <span class="keyword">start</span> <span class="keyword">with</span> <span class="keyword">id</span>=<span class="number">5</span> <span class="keyword">connect</span> <span class="keyword">by</span> <span class="keyword">prior</span> pid = <span class="keyword">id</span></span></span><br></pre></td></tr></table></figure></p>
<h2 id="Parallel"><a href="#Parallel" class="headerlink" title="Parallel"></a>Parallel</h2><p>　　在系统中，有2个4核心的CPU，那么一共8个逻辑CPU。每当ORACLE进行查询时，就会产生一个session，每个session占用一个CPU。当整个系统只有一个人在用的时候，剩下7个CPU，是空闲状态。那么就可以用parallel语句合理利用起来剩下7个CPU。<br>　　注意：当系统有很多用户连接时，开启parallel不是一个明确的选择。同时只有在需要<strong>处理一个很大的任务</strong>，如需要几个小时的作业中，并且要有足够的系统资源的情况下(这些资源包括cpu、内存和io)，才应该考虑使用parallel。否则，在一个多并发用户下，系统本身资源负担已经很大的情况下，启用parallel，将会导致某一个会话试图占用了所有的资源不，其他会话得不去等待，从而导致系统系能反而下降的情况。<br>　　Parallel分类：并行查询parallel query、并行dml parallel dml(pdml)、并行ddl parallel ddl(pddl)，语法：/<em>+parallel(table_short_name, cash_number)</em>/</p>
<h4 id="u5E76_u884C_u67E5_u8BE2"><a href="#u5E76_u884C_u67E5_u8BE2" class="headerlink" title="并行查询"></a>并行查询</h4><p>　　并行查询可以在查询语句，子查询语句中使用，但是不可以使用在一个远程引用的对象上（如DBLINK）。一个查询能够并行执行，需要满足一下条件：<br>（1）SQL语句中有Hint提示，比如Parallel 或者 Parallel_index.<br>（2）SQL语句中引用的对象被设置了并行属性。<br>（3）多表关联中，至少有一个表执行全表扫描（Full table scan）或者跨分区的Index range SCAN。<br><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 开启并行查询就是对于某个TABLE</span></span><br><span class="line"><span class="operator"><span class="keyword">alter</span> <span class="keyword">table</span> TABLE_NAME <span class="keyword">parallel</span> <span class="number">8</span></span><br><span class="line"><span class="comment">-- 将对于TABLE_NAME 这个表的查询改为8核</span></span><br><span class="line"><span class="keyword">select</span> <span class="comment">/*+ parallel(t1 8) */</span> <span class="keyword">count</span>(*) <span class="keyword">from</span> t1;</span></span><br><span class="line"><span class="comment">-- 只对于本次SQL有效</span></span><br><span class="line"><span class="comment">-- 测试不好用，没有加快，取消并行设置</span></span><br><span class="line"><span class="operator"><span class="keyword">alter</span> <span class="keyword">table</span> t1 noparallel</span></span><br></pre></td></tr></table></figure></p>
<h4 id="u5E76_u884Cdml"><a href="#u5E76_u884Cdml" class="headerlink" title="并行dml"></a>并行dml</h4><p>　　并行dml包括insert，update，delete，merge，在pdml期间，oracle可以使用多个并行执行服务器来执行insert，update，delete，merge，多个会话同时执行，同时每个会话(并发进程)都有自己的undo段，都是独立的一个事务，这些事务要么由pdml协调器进程提交，要么都rollback。在一个有充足I/o带宽的多cpu主机中，对于大规模的dml，速度可能会有很大的提升，尤其是在大型的数据仓库环境中。<br><figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; alter session enable parallel dml; 开启</span><br><span class="line">SQL&gt; alter session disable parallel dml; 关闭</span><br></pre></td></tr></table></figure></p>
<h4 id="Insert__u7684_u5E76_u884C_u64CD_u4F5C"><a href="#Insert__u7684_u5E76_u884C_u64CD_u4F5C" class="headerlink" title="Insert 的并行操作"></a>Insert 的并行操作</h4><p>　　实际上只有对于insert into … select … 这样的SQL语句启用并行才有意义。 对于insert into .. values… 并行没有意义，因为这条语句本身就是一个单条记录的操作。<br><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 操作insert和select分别使用并行，这两个并行是相互独立，互补干涉的，也可以单独使用其中的一个并行。</span></span><br><span class="line"><span class="operator"><span class="keyword">Insert</span> <span class="comment">/*+parallel(t 2) */</span> <span class="keyword">into</span> <span class="keyword">t</span> <span class="keyword">select</span> <span class="comment">/*+parallel(t1 2) */</span> * <span class="keyword">from</span> t1;</span></span><br></pre></td></tr></table></figure></p>
<p>　　Parallel比较邪恶，对开发者而言，不是好东西，会养成不好习惯，导致很多bad SQL不会暴漏，SQL Tuning的能力得不到提升</p>
<h2 id="u53C2_u8003_u94FE_u63A5"><a href="#u53C2_u8003_u94FE_u63A5" class="headerlink" title="参考链接"></a>参考链接</h2>
      
    </div>
    <footer class="article-footer">
      <a data-url="http://yoursite.com/2016/01/27/database/9.oracle学习/" data-id="cijzzpvgv00g31cfsshv0k6jy" class="article-share-link">Share</a>
      
      
  <ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/database/">database</a></li></ul>

    </footer>
  </div>
  
</article>


  
    <article id="post-java/17.缓存实现" class="article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    <a href="/2016/01/27/java/17.缓存实现/" class="article-date">
  <time datetime="2016-01-27T06:07:37.000Z" itemprop="datePublished">2016-01-27</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/categories/java/">java</a>
  </div>

  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 itemprop="name">
      <a class="article-title" href="/2016/01/27/java/17.缓存实现/">缓存实现</a>
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <h2 id="Terracotta"><a href="#Terracotta" class="headerlink" title="Terracotta"></a>Terracotta</h2><p>　　Terracotta的基本原理是对于集群间共享的数据，当在一个节点发生变化的时候，Terracotta只把变化的部分发送给Terracotta服务器，然后由服务器把它转发给真正需要这个数据的节点。这样对网络的压力就非常小，各个节点也不必浪费CPU时间和内存进行大量的序列化操作<br>　　terracotta支持HA，增加了系统的稳定性。同时也是开源的，并且可以集成在很多主流的开源软件中，如Jetty、Tomcat、Spring、Geronimo和EHCache。集群如下：<img src="/images/terracotta.png" alt="terracotta"></p>
<h2 id="LRU_u4E4BLinkedHashMap"><a href="#LRU_u4E4BLinkedHashMap" class="headerlink" title="LRU之LinkedHashMap"></a>LRU之LinkedHashMap</h2><p>　　LRU是Least Recently Used（最近最少使用）的缩写，也就是说，LRU缓存把最近最少使用的数据移除，让给最新读取的数据。要实现LRU缓存，首先要用到类 LinkedHashMap。用这个类有两大好处：一是<strong>它已经实现了按照访问顺序的存储</strong>，即最近读取的会放在最前面，最不常读取的会放在最后（当然，它也可以实现按照插 入顺序存储）。第二，<strong>它提供了一个方法用于判断是否需要移除最不常读取的数</strong>。但是，原始方法默认不需要移除（这时，LinkedHashMap相当于一个linkedlist），所以，我们需要override这样一个方法。要基于LinkedHashMap来实现LRU缓存，我们可以选择inheritance, 也可以选择 delegation，基于delegation的实现已经有人写出来了，代码如下：<br><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">LRUCache</span>&lt;<span class="title">K</span>, <span class="title">V</span>&gt; </span>&#123;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">static</span> <span class="keyword">final</span> <span class="keyword">float</span> hashTableLoadFactor = <span class="number">0.75f</span>;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">private</span> LinkedHashMap&lt;K, V&gt; map;</span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">int</span> cacheSize;</span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="title">LRUCache</span><span class="params">(<span class="keyword">int</span> cacheSize)</span> </span>&#123;</span><br><span class="line">        <span class="keyword">this</span>.cacheSize = cacheSize;</span><br><span class="line">        <span class="keyword">int</span> hashTableCapacity = (<span class="keyword">int</span>) Math.ceil(cacheSize / hashTableLoadFactor) + <span class="number">1</span>;</span><br><span class="line">        <span class="comment">// 第三个参数accessOrder - 排序方式</span></span><br><span class="line">        <span class="comment">// true ：按访问顺序排序</span></span><br><span class="line">        <span class="comment">// false ：按插入顺序排序</span></span><br><span class="line">        map = <span class="keyword">new</span> LinkedHashMap&lt;K, V&gt;(hashTableCapacity, hashTableLoadFactor, <span class="keyword">true</span>) &#123;</span><br><span class="line">            <span class="comment">/**</span><br><span class="line">             * 当新的entry通过put或者putAll方法插入时，该方法被调用</span><br><span class="line">             * 它提供了一个机制：当新添加一个entry时，是否移除eldest的entry</span><br><span class="line">             * 当该map作为一个cache时非常有用，通过删除老数据，减少内存消耗</span><br><span class="line">             * This is useful if the map represents a cache: it allows the map to reduce memory</span><br><span class="line">             */</span></span><br><span class="line">            <span class="annotation">@Override</span></span><br><span class="line">            <span class="function"><span class="keyword">protected</span> <span class="keyword">boolean</span> <span class="title">removeEldestEntry</span><span class="params">(Map.Entry&lt;K, V&gt; eldest)</span> </span>&#123;</span><br><span class="line">                <span class="keyword">return</span> size() &gt; LRUCache.<span class="keyword">this</span>.cacheSize;</span><br><span class="line">            &#125;</span><br><span class="line">        &#125;;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">synchronized</span> V <span class="title">get</span><span class="params">(K key)</span> </span>&#123;</span><br><span class="line">        <span class="keyword">return</span> map.get(key);</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">synchronized</span> <span class="keyword">void</span> <span class="title">put</span><span class="params">(K key, V value)</span> </span>&#123;</span><br><span class="line">        map.put(key, value);</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">synchronized</span> <span class="keyword">void</span> <span class="title">clear</span><span class="params">()</span> </span>&#123;</span><br><span class="line">        map.clear();</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">synchronized</span> <span class="keyword">int</span> <span class="title">usedEntries</span><span class="params">()</span> </span>&#123;</span><br><span class="line">        <span class="keyword">return</span> map.size();</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">synchronized</span> Collection&lt;Map.Entry&lt;K, V&gt;&gt; getAll() &#123;</span><br><span class="line">        <span class="keyword">return</span> <span class="keyword">new</span> ArrayList&lt;Map.Entry&lt;K, V&gt;&gt;(map.entrySet());</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="annotation">@Override</span></span><br><span class="line">    <span class="function"><span class="keyword">public</span> String <span class="title">toString</span><span class="params">()</span> </span>&#123;</span><br><span class="line">        <span class="keyword">return</span> map.toString();</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure></p>
<h2 id="LRU_u4E4B_u53CC_u94FE_u8868"><a href="#LRU_u4E4B_u53CC_u94FE_u8868" class="headerlink" title="LRU之双链表"></a>LRU之双链表</h2><p>　　双链表 + hashtable实现原理：将Cache的所有位置都用双连表连接起来，当一个位置被命中之后，就将通过调整链表的指向，将该位置调整到链表头的位置，新加入的Cache直接 加到链表头中。这样，在多次进行Cache操作后，最近被命中的，就会被向链表头方向移动，而没有命中的，而想链表后面移动，链表尾则表示最近最少使用的 Cache。链表的最后位置就是最少被命中的位置，需要淘汰是删除链表最后的部分即可。<br><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br><span class="line">110</span><br><span class="line">111</span><br><span class="line">112</span><br><span class="line">113</span><br><span class="line">114</span><br><span class="line">115</span><br><span class="line">116</span><br><span class="line">117</span><br><span class="line">118</span><br><span class="line">119</span><br><span class="line">120</span><br><span class="line">121</span><br><span class="line">122</span><br><span class="line">123</span><br><span class="line">124</span><br><span class="line">125</span><br><span class="line">126</span><br><span class="line">127</span><br><span class="line">128</span><br><span class="line">129</span><br><span class="line">130</span><br><span class="line">131</span><br><span class="line">132</span><br><span class="line">133</span><br><span class="line">134</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">LRUCacheTable</span>&lt;<span class="title">K</span>, <span class="title">V</span>&gt; </span>&#123;</span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">int</span> cacheSize;</span><br><span class="line">    <span class="keyword">private</span> Hashtable&lt;K, Entry&gt; nodes;<span class="comment">//缓存容器</span></span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">int</span> currentSize;</span><br><span class="line">    <span class="keyword">private</span> Entry first;<span class="comment">//链表头</span></span><br><span class="line">    <span class="keyword">private</span> Entry last;<span class="comment">//链表尾</span></span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="title">LRUCacheTable</span><span class="params">(<span class="keyword">int</span> i)</span> </span>&#123;</span><br><span class="line">        currentSize = <span class="number">0</span>;</span><br><span class="line">        cacheSize = i;</span><br><span class="line">        nodes = <span class="keyword">new</span> Hashtable&lt;K, Entry&gt;(i);<span class="comment">//缓存容器</span></span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="comment">/**</span><br><span class="line">     * 获取缓存中对象,并把它放在最前面</span><br><span class="line">     */</span></span><br><span class="line">    <span class="function"><span class="keyword">public</span> Entry <span class="title">get</span><span class="params">(K key)</span> </span>&#123;</span><br><span class="line">        Entry node = nodes.get(key);</span><br><span class="line">        <span class="keyword">if</span> (node != <span class="keyword">null</span>) &#123;</span><br><span class="line">            moveToHead(node);</span><br><span class="line">            <span class="keyword">return</span> node;</span><br><span class="line">        &#125; <span class="keyword">else</span> &#123;</span><br><span class="line">            <span class="keyword">return</span> <span class="keyword">null</span>;</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="comment">/**</span><br><span class="line">     * 添加entry到hashtable, 并把entry</span><br><span class="line">     */</span></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">put</span><span class="params">(K key, V value)</span> </span>&#123;</span><br><span class="line">        <span class="comment">//先查看hashtable是否存在该entry, 如果存在，则只更新其value</span></span><br><span class="line">        Entry node = nodes.get(key);</span><br><span class="line"></span><br><span class="line">        <span class="keyword">if</span> (node == <span class="keyword">null</span>) &#123;</span><br><span class="line">            <span class="comment">//缓存容器是否已经超过大小.</span></span><br><span class="line">            <span class="keyword">if</span> (currentSize &gt;= cacheSize) &#123;</span><br><span class="line">                nodes.remove(last.key);</span><br><span class="line">                removeLast();</span><br><span class="line">            &#125; <span class="keyword">else</span> &#123;</span><br><span class="line">                currentSize++;</span><br><span class="line">            &#125;</span><br><span class="line">            node = <span class="keyword">new</span> Entry();</span><br><span class="line">        &#125;</span><br><span class="line">        node.key = key;</span><br><span class="line">        node.value = value;</span><br><span class="line">        <span class="comment">//将最新使用的节点放到链表头，表示最新使用的.</span></span><br><span class="line">        moveToHead(node);</span><br><span class="line">        nodes.put(key, node);</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="comment">/**</span><br><span class="line">     * 将entry删除, 注意：删除操作只有在cache满了才会被执行</span><br><span class="line">     */</span></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">remove</span><span class="params">(K key)</span> </span>&#123;</span><br><span class="line">        Entry node = nodes.get(key);</span><br><span class="line">        <span class="comment">//在链表中删除</span></span><br><span class="line">        <span class="keyword">if</span> (node != <span class="keyword">null</span>) &#123;</span><br><span class="line">            <span class="keyword">if</span> (node.prev != <span class="keyword">null</span>) &#123;</span><br><span class="line">                node.prev.next = node.next;</span><br><span class="line">            &#125;</span><br><span class="line">            <span class="keyword">if</span> (node.next != <span class="keyword">null</span>) &#123;</span><br><span class="line">                node.next.prev = node.prev;</span><br><span class="line">            &#125;</span><br><span class="line">            <span class="keyword">if</span> (last == node)</span><br><span class="line">                last = node.prev;</span><br><span class="line">            <span class="keyword">if</span> (first == node)</span><br><span class="line">                first = node.next;</span><br><span class="line">        &#125;</span><br><span class="line">        <span class="comment">//在hashtable中删除</span></span><br><span class="line">        nodes.remove(key);</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="comment">/**</span><br><span class="line">     * 删除链表尾部节点，即使用最后 使用的entry</span><br><span class="line">     */</span></span><br><span class="line">    <span class="function"><span class="keyword">private</span> <span class="keyword">void</span> <span class="title">removeLast</span><span class="params">()</span> </span>&#123;</span><br><span class="line">        <span class="comment">//链表尾不为空,则将链表尾指向null. 删除连表尾（删除最少使用的缓存对象）</span></span><br><span class="line">        <span class="keyword">if</span> (last != <span class="keyword">null</span>) &#123;</span><br><span class="line">            <span class="keyword">if</span> (last.prev != <span class="keyword">null</span>)</span><br><span class="line">                last.prev.next = <span class="keyword">null</span>;</span><br><span class="line">            <span class="keyword">else</span></span><br><span class="line">                first = <span class="keyword">null</span>;</span><br><span class="line">            last = last.prev;</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="comment">/**</span><br><span class="line">     * 移动到链表头，表示这个节点是最新使用过的</span><br><span class="line">     */</span></span><br><span class="line">    <span class="function"><span class="keyword">private</span> <span class="keyword">void</span> <span class="title">moveToHead</span><span class="params">(Entry node)</span> </span>&#123;</span><br><span class="line">        <span class="keyword">if</span> (node == first)</span><br><span class="line">            <span class="keyword">return</span>;</span><br><span class="line">        <span class="keyword">if</span> (node.prev != <span class="keyword">null</span>)</span><br><span class="line">            node.prev.next = node.next;</span><br><span class="line">        <span class="keyword">if</span> (node.next != <span class="keyword">null</span>)</span><br><span class="line">            node.next.prev = node.prev;</span><br><span class="line">        <span class="keyword">if</span> (last == node)</span><br><span class="line">            last = node.prev;</span><br><span class="line">        <span class="keyword">if</span> (first != <span class="keyword">null</span>) &#123;</span><br><span class="line">            node.next = first;</span><br><span class="line">            first.prev = node;</span><br><span class="line">        &#125;</span><br><span class="line">        first = node;</span><br><span class="line">        node.prev = <span class="keyword">null</span>;</span><br><span class="line">        <span class="keyword">if</span> (last == <span class="keyword">null</span>)</span><br><span class="line">            last = first;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="comment">/**</span><br><span class="line">     * 清空缓存</span><br><span class="line">     */</span></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">clear</span><span class="params">()</span> </span>&#123;</span><br><span class="line">        first = <span class="keyword">null</span>;</span><br><span class="line">        last = <span class="keyword">null</span>;</span><br><span class="line">        currentSize = <span class="number">0</span>;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="annotation">@Override</span></span><br><span class="line">    <span class="function"><span class="keyword">public</span> String <span class="title">toString</span><span class="params">()</span> </span>&#123;</span><br><span class="line">        <span class="keyword">return</span> nodes.toString();</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="class"><span class="keyword">class</span> <span class="title">Entry</span> </span>&#123;</span><br><span class="line">        Entry prev;<span class="comment">//前一节点</span></span><br><span class="line">        Entry next;<span class="comment">//后一节点</span></span><br><span class="line">        Object value;<span class="comment">//值</span></span><br><span class="line">        Object key;<span class="comment">//键</span></span><br><span class="line"></span><br><span class="line">        <span class="annotation">@Override</span></span><br><span class="line">        <span class="function"><span class="keyword">public</span> String <span class="title">toString</span><span class="params">()</span> </span>&#123;</span><br><span class="line">            <span class="keyword">return</span> key + <span class="string">": "</span> + value;</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure></p>
<p>　　注意：该实现仅仅是记录了最频繁的数据和最不频繁的数据，hashtable内部并不保证数据的顺序</p>
<h2 id="HashMap_u548CHashTable"><a href="#HashMap_u548CHashTable" class="headerlink" title="HashMap和HashTable"></a>HashMap和HashTable</h2><p>　　HashMap是一个数组和链表的结合体（在数据结构称“链表散列“），如下图示：<img src="/images/hashmap.jpeg" alt="hashmap"><br>　　往HashMap中put元素的时候，先根据key的hash值得到这个元素在数组中的位置（即下标），然后就可以把这个元素放到对应的位置中 了。如果这个元素所在的位子上已经存放有其他元素了，那么在同一个位子上的元素将以链表的形式存放，新加入的放在链头，最先加入的放在链尾。<br>　　HashTable和HashMap区别：<br>　　1. 继承不同。<br>public class Hashtable extends Dictionary implements Map<br>public class HashMap  extends AbstractMap implements Map<br>　　2. <strong>Hashtable中的方法是同步的</strong>，而HashMap中的方法在缺省情况下是非同步的。在多线程并发的环境下，可以直接使用Hashtable，但是要使用HashMap的话就要自己增加同步处理了。<br>　　3. Hashtable中，key和value都不允许出现null值。在HashMap中，null可以作为键，这样的键只有一个；可以有一个或多个键所对应的值为null。当get()方法返回null值时，即可以表示 HashMap中没有该键，也可以表示该键所对应的值为null。因此，在HashMap中不能由get()方法来判断HashMap中是否存在某个键，而应该用containsKey()方法来判断。<br>　　4. 两个遍历方式的内部实现上不同。Hashtable、HashMap都使用了 Iterator。而由于历史原因，Hashtable还使用了Enumeration的方式 。<br>　　5. 哈希值的使用不同，HashTable直接使用对象的hashCode。而HashMap重新计算hash值。<br>　　6. Hashtable和HashMap它们两个内部实现方式的数组的初始大小和扩容的方式。HashTable中hash数组默认大小是11，增加的方式是 old*2+1。HashMap中hash数组的默认大小是16，而且一定是2的指数。 </p>
<h2 id="ConcurrentLinkedHashMap"><a href="#ConcurrentLinkedHashMap" class="headerlink" title="ConcurrentLinkedHashMap"></a>ConcurrentLinkedHashMap</h2><p>　　java未提供改实现，需要引入包：’com.googlecode.concurrentlinkedhashmap:concurrentlinkedhashmap-lru:1.4.2’。cassandra也在concurrentLinkedHashMap的基础上实现了LRUCache。<br><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">LRUConcurrentLinkedHashMap</span>&lt;<span class="title">K</span>, <span class="title">V</span>&gt; </span>&#123;</span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">final</span> <span class="keyword">int</span> DEFAULT_CONCURENCY_LEVEL = <span class="number">64</span>;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">final</span> ConcurrentLinkedHashMap&lt;K, V&gt; map;</span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">final</span> AtomicLong requests = <span class="keyword">new</span> AtomicLong(<span class="number">0</span>);</span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">final</span> AtomicLong hits = <span class="keyword">new</span> AtomicLong(<span class="number">0</span>);</span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">final</span> AtomicLong lastRequests = <span class="keyword">new</span> AtomicLong(<span class="number">0</span>);</span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">final</span> AtomicLong lastHits  = <span class="keyword">new</span> AtomicLong(<span class="number">0</span>);</span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">volatile</span> <span class="keyword">boolean</span> capacitySetManually;</span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="title">LRUConcurrentLinkedHashMap</span><span class="params">(<span class="keyword">int</span> capacity)</span> </span>&#123;</span><br><span class="line">        <span class="keyword">this</span>(capacity, DEFAULT_CONCURENCY_LEVEL);</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="title">LRUConcurrentLinkedHashMap</span><span class="params">(<span class="keyword">int</span> capacity, <span class="keyword">int</span> concurrency)</span> </span>&#123;</span><br><span class="line">        map = <span class="keyword">new</span> ConcurrentLinkedHashMap.Builder&lt;K, V&gt;().weigher(Weighers.&lt;V&gt; singleton())</span><br><span class="line">                .initialCapacity(capacity).maximumWeightedCapacity(capacity)</span><br><span class="line">                .concurrencyLevel(concurrency).build();</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">put</span><span class="params">(K key, V value)</span> </span>&#123;</span><br><span class="line">        map.put(key, value);</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">public</span> V <span class="title">get</span><span class="params">(K key)</span> </span>&#123;</span><br><span class="line">        V v = map.get(key);</span><br><span class="line">        requests.incrementAndGet();</span><br><span class="line">        <span class="keyword">if</span>(v != <span class="keyword">null</span>)</span><br><span class="line">            hits.incrementAndGet();</span><br><span class="line">        <span class="keyword">return</span> v;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">public</span> V <span class="title">getInternal</span><span class="params">(K key)</span> </span>&#123;</span><br><span class="line">        <span class="keyword">return</span> map.get(key);</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">remove</span><span class="params">(K key)</span> </span>&#123;</span><br><span class="line">        map.remove(key);</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">long</span> <span class="title">getCapacity</span><span class="params">()</span> </span>&#123;</span><br><span class="line">        <span class="keyword">return</span> map.capacity();</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">boolean</span> <span class="title">isCapacitySetManually</span><span class="params">()</span> </span>&#123;</span><br><span class="line">        <span class="keyword">return</span> capacitySetManually;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">updateCapacity</span><span class="params">(<span class="keyword">int</span> capacity)</span> </span>&#123;</span><br><span class="line">        map.setCapacity(capacity);</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">setCapacity</span><span class="params">(<span class="keyword">int</span> capacity)</span> </span>&#123;</span><br><span class="line">        updateCapacity(capacity);</span><br><span class="line">        capacitySetManually = <span class="keyword">true</span>;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">int</span> <span class="title">getSize</span><span class="params">()</span> </span>&#123;</span><br><span class="line">        <span class="keyword">return</span> map.size();</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">long</span> <span class="title">getHits</span><span class="params">()</span> </span>&#123;</span><br><span class="line">        <span class="keyword">return</span> hits.get();</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">long</span> <span class="title">getRequests</span><span class="params">()</span> </span>&#123;</span><br><span class="line">        <span class="keyword">return</span> requests.get();</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">double</span> <span class="title">getRecentHitRate</span><span class="params">()</span> </span>&#123;</span><br><span class="line">        <span class="keyword">long</span> r = requests.get();</span><br><span class="line">        <span class="keyword">long</span> h = hits.get();</span><br><span class="line">        <span class="keyword">try</span>&#123;</span><br><span class="line">            <span class="keyword">return</span>((<span class="keyword">double</span>) (h - lastHits.get())) / (r - lastRequests.get());</span><br><span class="line">        &#125; <span class="keyword">finally</span>&#123;</span><br><span class="line">            lastRequests.set(r);</span><br><span class="line">            lastHits.set(h);</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">clear</span><span class="params">()</span> </span>&#123;</span><br><span class="line">        map.clear();</span><br><span class="line">        requests.set(<span class="number">0</span>);</span><br><span class="line">        hits.set(<span class="number">0</span>);</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">public</span> Set&lt;K&gt; <span class="title">getKeySet</span><span class="params">()</span> </span>&#123;</span><br><span class="line">        <span class="keyword">return</span> map.keySet();</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure></p>
<p>　　源码下载地址：<img src="/code/cache-demo.zip" alt="cache-demo.zip"></p>
<h2 id="u53C2_u8003_u94FE_u63A5"><a href="#u53C2_u8003_u94FE_u63A5" class="headerlink" title="参考链接"></a>参考链接</h2><p><a href="http://blog.csdn.net/beiyeqingteng/article/details/7010411" target="_blank" rel="external">http://blog.csdn.net/beiyeqingteng/article/details/7010411</a></p>

      
    </div>
    <footer class="article-footer">
      <a data-url="http://yoursite.com/2016/01/27/java/17.缓存实现/" data-id="cijzzpvdz00d71cfs6j8rum07" class="article-share-link">Share</a>
      
      
  <ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/数据结构/">数据结构</a></li></ul>

    </footer>
  </div>
  
</article>


  
    <article id="post-database/8.mysql性能分析" class="article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    <a href="/2016/01/24/database/8.mysql性能分析/" class="article-date">
  <time datetime="2016-01-24T03:27:00.000Z" itemprop="datePublished">2016-01-24</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/categories/database/">database</a>
  </div>

  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 itemprop="name">
      <a class="article-title" href="/2016/01/24/database/8.mysql性能分析/">mysql性能分析</a>
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <h2 id="explain"><a href="#explain" class="headerlink" title="explain"></a>explain</h2><p>　　EXPLAIN SELECT * FROM <code>TABLE_NAME</code>;</p>
<table>
<thead>
<tr>
<th style="text-align:center">id</th>
<th style="text-align:center">select_type</th>
<th style="text-align:center">table</th>
<th style="text-align:center">type</th>
<th style="text-align:center">possible_keys</th>
<th style="text-align:center">key</th>
<th style="text-align:center">key_len</th>
<th style="text-align:center">ref</th>
<th style="text-align:center">rows</th>
<th style="text-align:center">Extra</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align:center">1</td>
<td style="text-align:center">SIMPLE</td>
<td style="text-align:center">table_name</td>
<td style="text-align:center">ALL</td>
<td style="text-align:center">NULL</td>
<td style="text-align:center">NULL</td>
<td style="text-align:center">NULL</td>
<td style="text-align:center">NULL</td>
<td style="text-align:center">100</td>
</tr>
</tbody>
</table>
<p>　　1. table #显示该语句涉及的表<br>　　2. <strong>type</strong> #这列很重要，显示了连接使用了哪种类别,有无使用索引，反映语句的质量。<br>　　3. possible_keys #列指出MySQL能使用哪个索引在该表中找到行<br>　　4. key #显示MySQL实际使用的键（索引）。如果没有选择索引，键是NULL。<br>　　5. key_len #显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好<br>　　6. ref #显示使用哪个列或常数与key一起从表中选择行。<br>　　7. rows #显示MySQL认为它执行查询时必须检查的行数。<br>　　8. extra #包含MySQL解决查询的详细信息。<br>　　<strong>type</strong>显示的是访问类型，结果值从好到坏依次是：<br>system &gt; const &gt; eq_ref &gt; ref &gt; fulltext &gt; ref_or_null &gt; index_merge &gt; unique_subquery &gt; index_subquery &gt; range &gt; index &gt; ALL（优–&gt;差）。一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。</p>
<h2 id="u6162_u67E5_u8BE2_u65E5_u5FD7"><a href="#u6162_u67E5_u8BE2_u65E5_u5FD7" class="headerlink" title="慢查询日志"></a>慢查询日志</h2><p>　　按照《mysql编译安装》安装mysql-5.6.24后，看看慢日志查询的配置情况，如下所示：<img src="/images/mysql-slow-query.png" alt="慢查询日志"><br>　　完整配置如下：<br><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 开启慢查询日志，运行中mysql立即生效，重启后失效</span></span><br><span class="line"><span class="operator"><span class="keyword">set</span> <span class="keyword">global</span> slow_query_log = <span class="keyword">on</span>;</span></span><br><span class="line"><span class="operator"><span class="keyword">set</span> <span class="keyword">global</span> long_query_time=<span class="number">1</span>;</span></span><br><span class="line">#要通过<span class="operator"><span class="keyword">show</span>立刻看到效果，需要先关闭客户端，重新连接</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 重启后仍然生效配置：</span></span><br><span class="line">修改/usr/<span class="keyword">local</span>/mysql/my.cnf，[mysqld]下增加如下内容</span><br><span class="line">long_query_time=<span class="number">1</span></span><br><span class="line">slow_query_log=<span class="number">1</span></span><br><span class="line"><span class="comment">-- 慢查询日志文件位置</span></span><br><span class="line">slow_query_log_file</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 关闭myslq服务后再启动</span></span><br><span class="line">service mysqld <span class="keyword">stop</span>;</span></span><br><span class="line">service mysqld <span class="operator"><span class="keyword">start</span>;</span></span><br></pre></td></tr></table></figure></p>
<p>　　MySQL自带了mysqldumpslow工具用来分析slow query日志，或者其它工具也可以，通过工具配合可以更好的分析。</p>
<h2 id="Profiling"><a href="#Profiling" class="headerlink" title="Profiling"></a>Profiling</h2><p>　　<strong>慢查询日志负责找出需要优化的语句（即耗时语句），再采用Profiling对语句进行优化。</strong><br>　　MySQL的Query Profiler是一个使用非常方便的 Query 诊断分析工具，通过该工具可以获取一条Query 在整个执行过程中多种资源的消耗情况，如CPU，IO，IPC，SWAP等，以及发生的PAGE FAULTS，CONTEXT SWITCHE等等，同时还能得到该Query执行过程中MySQL所调用的各个函数在源文件中的位置。下面我们看看 Query Profiler 的具体用法。<br>　　1、开启profiling参数：set profiling=1;<br>　　2、执行Query：select message_id,count(*) from t_message_push_info group by message_id;在开启 Query Profiler 功能之后，MySQL 就会自动记录所有执行的 Query 的 profile 信息了。<br>　　3、获取系统中保存的所有 Query 的 profile 信息：执行 “SHOW PROFILE” 命令即可。如下图所示：<img src="/images/mysql-profile.png" alt="mysql-profile"><br>　　4、针对单个 Query 获取详细的 profile 信息。在获取到概要信息之后，可以根据概要信息中的 Query_ID 来获取某个 Query 在执行过程中详细的 profile 信息了，具体操作如下：show profile cpu, block io for query 11;如下图所示：<img src="/images/mysql-profile-queryid.png" alt="sql执行详情"><br>　　上面的例子中是获取 CPU 和 Block IO 的消耗，非常清晰，对于定位性能瓶颈非常适用。希望得到取其他的信息，都可以通过执行 “SHOW PROFILE <em>*</em> FOR QUERY n” 来获取，各位读者朋友可以自行测试熟悉。</p>
<h2 id="u6700_u5927_u8FDE_u63A5_u6570"><a href="#u6700_u5927_u8FDE_u63A5_u6570" class="headerlink" title="最大连接数"></a>最大连接数</h2><p>　　set global max_connections = 500;　#max_user_connections为某个用户的最大连接数<br>　　修改my.ini或my.cnf，查找max_connections，修改为max_connections=1000<br>　　查看当前的连接情况：show full processlist;，如下图所示：<img src="/images/mysql-connections.png" alt="mysql-connections"></p>
<h2 id="u5206_u533A"><a href="#u5206_u533A" class="headerlink" title="分区"></a>分区</h2><p>　　删除表分区会造成该表分区内部数据也一起被删除掉<br>　　Rang分区、List分区、Hash分区（确保数据在预先确定数目的分区中平均分布）和Key分区，一个数据表最多只能有1024个分区</p>
<h4 id="List_u5206_u533A"><a href="#List_u5206_u533A" class="headerlink" title="List分区"></a>List分区</h4><figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br></pre></td><td class="code"><pre><span class="line">CREATE TABLE employees (</span><br><span class="line">    id INT NOT NULL,</span><br><span class="line">    fname VARCHAR(30),</span><br><span class="line">    lname VARCHAR(30),</span><br><span class="line">    hired DATE NOT NULL DEFAULT '1970-01-01',</span><br><span class="line">    separated DATE NOT NULL DEFAULT '9999-12-31',</span><br><span class="line">    job_code INT,</span><br><span class="line">    store_id INT</span><br><span class="line">)PARTITION BY LIST(store_id)(</span><br><span class="line">    PARTITION pNorth VALUES IN (3,5,6,9,17),</span><br><span class="line">    PARTITION pEast VALUES IN (1,2,10,11,19,20),</span><br><span class="line">    PARTITION pWest VALUES IN (4,12,13,14,18),</span><br><span class="line">    PARTITION pCentral VALUES IN (7,8,15,16)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line">DELIMITER $$</span><br><span class="line">DROP PROCEDURE IF EXISTS SampleProc$$</span><br><span class="line">CREATE PROCEDURE SampleProc()</span><br><span class="line">    BEGIN</span><br><span class="line">        DECLARE x INT;</span><br><span class="line">        SET x = 1;</span><br><span class="line">        WHILE x&lt;= 20 DO</span><br><span class="line">            insert into employees(id,fname,lname,hired,separated,job_code,store_id) values(x,concat('firstname',x),concat('ai',x),'1994-01-01','1999-01-01',10,x);</span><br><span class="line">            SET  x = x + 1;</span><br><span class="line">        END WHILE;</span><br><span class="line">    END$$</span><br><span class="line">/*执行存储过程*/</span><br><span class="line">call SampleProc() $$</span><br><span class="line">delimiter ;</span><br></pre></td></tr></table></figure>
<p>　　查看是否用到了分区：<br><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="operator"><span class="keyword">explain</span> <span class="keyword">SELECT</span> <span class="keyword">count</span>(*) <span class="keyword">FROM</span> employees <span class="keyword">WHERE</span> store_id = <span class="number">1</span> <span class="keyword">GROUP</span> <span class="keyword">BY</span> store_id;</span></span><br><span class="line"><span class="comment">-- store_id所在的分区有6条数据（表中所有数据为20条），本次查询只扫描了6条数据，使用到了分区</span></span><br></pre></td></tr></table></figure></p>
<h4 id="Hash_u5206_u533A"><a href="#Hash_u5206_u533A" class="headerlink" title="Hash分区"></a>Hash分区</h4><figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br></pre></td><td class="code"><pre><span class="line">CREATE TABLE employees (</span><br><span class="line">    id INT NOT NULL,</span><br><span class="line">    fname VARCHAR(30),</span><br><span class="line">    lname VARCHAR(30),</span><br><span class="line">    hired DATE NOT NULL DEFAULT '1970-01-01',</span><br><span class="line">    separated DATE NOT NULL DEFAULT '9999-12-31',</span><br><span class="line">    job_code INT,</span><br><span class="line">    store_id INT</span><br><span class="line">)PARTITION BY HASH(store_id)</span><br><span class="line">    PARTITIONS 4;</span><br><span class="line"> </span><br><span class="line">DELIMITER $$</span><br><span class="line">DROP PROCEDURE IF EXISTS SampleProc$$</span><br><span class="line">CREATE PROCEDURE SampleProc()</span><br><span class="line">    BEGIN</span><br><span class="line">        DECLARE x INT;</span><br><span class="line">        SET x = 1;</span><br><span class="line">        WHILE x&lt;= 20 DO</span><br><span class="line">            insert into employees(id,fname,lname,hired,separated,job_code,store_id) values(x,concat('firstname',x),concat('ai',x),'1994-01-01','1999-01-01',10,x);</span><br><span class="line">            SET  x = x + 1;</span><br><span class="line">        END WHILE;</span><br><span class="line">    END$$</span><br><span class="line">/*执行存储过程*/</span><br><span class="line">call SampleProc() $$</span><br><span class="line">delimiter ;</span><br><span class="line"></span><br><span class="line">-- 正常使用了分区 </span><br><span class="line">explain SELECT count(*) FROM employees WHERE store_id = 1 GROUP BY store_id;</span><br></pre></td></tr></table></figure>
<h4 id="Key_u5206_u533A"><a href="#Key_u5206_u533A" class="headerlink" title="Key分区"></a>Key分区</h4><figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br></pre></td><td class="code"><pre><span class="line">CREATE TABLE employees (</span><br><span class="line">    id INT NOT NULL,</span><br><span class="line">    fname VARCHAR(30),</span><br><span class="line">    lname VARCHAR(30),</span><br><span class="line">    hired DATE NOT NULL DEFAULT '1970-01-01',</span><br><span class="line">    separated DATE NOT NULL DEFAULT '9999-12-31',</span><br><span class="line">    job_code INT,</span><br><span class="line">    store_id INT</span><br><span class="line">)PARTITION BY LINEAR KEY (store_id)</span><br><span class="line">    PARTITIONS 5;</span><br><span class="line">     </span><br><span class="line">DELIMITER $$</span><br><span class="line">DROP PROCEDURE IF EXISTS SampleProc$$</span><br><span class="line">CREATE PROCEDURE SampleProc()</span><br><span class="line">    BEGIN</span><br><span class="line">        DECLARE x INT;</span><br><span class="line">        SET x = 1;</span><br><span class="line">        WHILE x&lt;= 20 DO</span><br><span class="line">            insert into employees(id,fname,lname,hired,separated,job_code,store_id) values(x,concat('firstname',x),concat('ai',x),'1994-01-01','1999-01-01',10,x);</span><br><span class="line">            SET  x = x + 1;</span><br><span class="line">        END WHILE;</span><br><span class="line">    END$$</span><br><span class="line">/*执行存储过程*/</span><br><span class="line">call SampleProc() $$</span><br><span class="line">delimiter ;</span><br><span class="line">## 查看扫描的行数</span><br><span class="line">explain SELECT count(*) FROM employees WHERE store_id = 1 GROUP BY store_id;</span><br></pre></td></tr></table></figure>
<h2 id="u53C2_u8003_u94FE_u63A5"><a href="#u53C2_u8003_u94FE_u63A5" class="headerlink" title="参考链接"></a>参考链接</h2><p><a href="http://baike.baidu.com/link?url=pA9eRUAvufYZDOWn1dcJTSRVKBrYWFKmS4RD_3OFhFounIqjfJsNz7Jkxz_AHDs9fjW434tmWiJfA-zyW5gREa" target="_blank" rel="external">http://baike.baidu.com/link?url=pA9eRUAvufYZDOWn1dcJTSRVKBrYWFKmS4RD_3OFhFounIqjfJsNz7Jkxz_AHDs9fjW434tmWiJfA-zyW5gREa</a><br><a href="http://asyty.iteye.com/blog/1203016" target="_blank" rel="external">http://asyty.iteye.com/blog/1203016</a></p>

      
    </div>
    <footer class="article-footer">
      <a data-url="http://yoursite.com/2016/01/24/database/8.mysql性能分析/" data-id="cijzzpvh400g81cfsv22wlcw8" class="article-share-link">Share</a>
      
      
  <ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/database/">database</a></li></ul>

    </footer>
  </div>
  
</article>


  
    <article id="post-database/7.mysql使用笔记" class="article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    <a href="/2016/01/22/database/7.mysql使用笔记/" class="article-date">
  <time datetime="2016-01-22T08:52:00.000Z" itemprop="datePublished">2016-01-22</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/categories/database/">database</a>
  </div>

  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 itemprop="name">
      <a class="article-title" href="/2016/01/22/database/7.mysql使用笔记/">mysql使用笔记</a>
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <h2 id="u9884_u5199_u5F0F_u65E5_u5FD7_uFF08Write-Ahead_Logging_uFF09"><a href="#u9884_u5199_u5F0F_u65E5_u5FD7_uFF08Write-Ahead_Logging_uFF09" class="headerlink" title="预写式日志（Write-Ahead Logging）"></a>预写式日志（Write-Ahead Logging）</h2><p>　　存储引擎在修改表数据时，只修改数据在内存的拷贝，再将该修改行为记录到持久的硬盘上的事物日志中，而不是每次都将数据本身持久化到磁盘。<br>　　事物日志采用追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事物日志的方式快很多。事物日志持久以后，内存中被修改的数据在后台可以慢慢的刷回到磁盘中。这种方式称为预写式日志（Write-Ahead Logging），修改数据需要写两次磁盘。<br>　　事物日志已经持久化，但数据未刷入磁盘。系统崩溃，再次启动时，存储引擎自动恢复这部分数据</p>
<h2 id="u8FDC_u7A0B_u8BBF_u95EE"><a href="#u8FDC_u7A0B_u8BBF_u95EE" class="headerlink" title="远程访问"></a>远程访问</h2><p>　　Mysql默认是不可以通过远程机器访问的，通过下面的配置可以开启远程访问<br><figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">#命令行下mysql -uroot -p 进入到mysql客户端</span><br><span class="line">&gt; use mysql;</span><br><span class="line">&gt; GRANT ALL ON *.* TO admin@'%' IDENTIFIED BY 'admin' WITH GRANT OPTION;</span><br><span class="line">#含义：允许任何IP地址（上面的 % 就是这个意思）的电脑用admin帐户和密码（admin）来访问这个MySQL Server</span><br><span class="line">#root帐户是默认无法远程登陆的，做以下调整即可</span><br><span class="line">&gt; GRANT ALL ON *.* TO root@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;</span><br><span class="line"></span><br><span class="line">#注意：% 不包括localhost</span><br><span class="line">#异常如下：ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)</span><br><span class="line">#在mysql中输入如下命令即可解决</span><br><span class="line">&gt; GRANT ALL ON *.* TO admin@'localhost' IDENTIFIED BY 'admin' WITH GRANT OPTION;</span><br><span class="line"></span><br><span class="line">#查询mysql的用户，可能需要先执行 flush privileges;命令</span><br><span class="line">&gt; select host,user,password from mysql.user;</span><br><span class="line">#flush privileges表示从grant表中重新加载权限数据。因为MySQL把权限都放在了cache中，所以在做完更改后需要重新加载。</span><br></pre></td></tr></table></figure></p>
<h2 id="u6570_u5B57_u7C7B_u578B"><a href="#u6570_u5B57_u7C7B_u578B" class="headerlink" title="数字类型"></a>数字类型</h2><p>　　mysql有多种数字类型，主要有int、bigint、smallint和tinyint</p>
<table>
<thead>
<tr>
<th style="text-align:left">类型</th>
<th style="text-align:left">说明</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align:left">bigint</td>
<td style="text-align:left">从 -2^63 到 2^63-1 的整型数据。<strong>8个字节</strong>。<br>在mysql建表中的length，只是用于显示的位数</td>
</tr>
<tr>
<td style="text-align:left">int</td>
<td style="text-align:left">从 -2^31 到 2^31 – 1 的整型数据。<strong>4个字节</strong>。<br>int 的 SQL-92 同义字为 integer</td>
</tr>
<tr>
<td style="text-align:left">smallint</td>
<td style="text-align:left">从 -2^15 到 2^15–1 的整型数据。存储大小为 2 个字节</td>
</tr>
<tr>
<td style="text-align:left">tinyint</td>
<td style="text-align:left">从 0 到 255 的整型数据，存储大小为 1 字节</td>
</tr>
</tbody>
</table>
<p>　　注意：int(M) 在 integer 数据类型中，M 表示最大显示宽度。M 的值跟 int(M)所占多少存储空间并无任何关系，和数字位数也无关系。int(3)、int(4)、int(8) 在磁盘上都是占用 4 btyes 的存储空间</p>
<h2 id="u6570_u636E_u5BFC_u51FA"><a href="#u6570_u636E_u5BFC_u51FA" class="headerlink" title="数据导出"></a>数据导出</h2><figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">-- &#21629;&#20196;&#34892;&#19979;&#29992;&#27861;&#65306;mysqldump -u&#29992;&#25143;&#21517; -p&#23494;&#30721; -d &#25968;&#25454;&#24211;&#21517; &#34920;&#21517; &#33050;&#26412;&#21517;;&#10;&#10;-- &#23548;&#20986;&#24211;&#30340;&#34920;&#21644;&#35270;&#22270;&#31561;&#32467;&#26500;&#65288;&#20854;&#20013;&#29992;&#25142;&#21517;&#28858;root,&#23494;&#30721;&#28858;dbpasswd&#65292;&#29983;&#25104;&#30340;&#33050;&#26412;&#21517;&#28858;db.sql&#65289;&#10;&#62; mysqldump -u root -p dbpasswd -d &#25968;&#25454;&#24211;&#21517; &#62; db.sql;&#10;&#10;-- &#23548;&#20986;&#24211;&#30340;&#26576;&#24352;&#34920;(test)&#32467;&#26500;&#10;&#62; mysqldump -u root -p dbpasswd -d &#25968;&#25454;&#24211;&#21517; test &#62; db.sql;&#10;&#10;-- &#23548;&#20986;&#34920;(test)&#25968;&#25454;&#10;&#62; mysqldump -u root -p dbpasswd  -t &#25968;&#25454;&#24211;&#21517; test &#62; db.sql;&#10;&#10;-- &#23548;&#20986;&#34920;&#32467;&#26500;&#21450;&#25968;&#25454;&#65288;&#19981;&#21152;-d, -t&#65289;&#10;&#62; mysqldump -u root -p dbpasswd  &#25968;&#25454;&#24211;&#21517; &#62; db.sql;&#10;&#10;-- &#23548;&#20986;&#25968;&#25454;&#24211;&#20026;dbname&#26576;&#24352;&#34920;(test)&#32467;&#26500;&#21450;&#34920;&#25968;&#25454;&#65288;&#19981;&#21152;-d&#65289;&#10;&#62; mysqldump -uroot -pdbpasswd dbname test &#62; db.sql;&#10;&#10;-- --where/-w&#21442;&#25968;&#21487;&#20197;&#35774;&#23450;&#25968;&#25454;&#23548;&#20986;&#30340;&#26465;&#20214;&#65292;&#20351;&#29992;&#26041;&#24335;&#21644;SQL&#26597;&#35810;&#21629;&#20196;&#20013;&#20013;&#30340;where&#22522;&#26412;&#19978;&#30456;&#21516;&#10;&#62; mysqldump -u&#29992;&#25143;&#21517; -p&#23494;&#30721; &#25968;&#25454;&#24211;&#21517; &#34920;&#21517; --where=&#34;&#31579;&#36873;&#26465;&#20214;&#34; &#62; &#23548;&#20986;&#25991;&#20214;&#36335;&#24452;&#10;&#10;-- &#20174;meteo&#25968;&#25454;&#24211;&#30340;sdata&#34920;&#20013;&#23548;&#20986;sensorid=11 &#19988; fieldid=0&#30340;&#25968;&#25454;&#21040; /home/xyx/Temp.sql &#36825;&#20010;&#25991;&#20214;&#20013;&#10;&#62; mysqldump -uroot -p123456 meteo sdata --where=&#34; sensorid=11 and fieldid=0&#34; &#62; /home/xyx/Temp.sql&#10;&#10;-- &#30452;&#25509;&#23548;&#20986;&#25991;&#26412;&#25991;&#20214;*.txt&#10;&#62; mysqldump -uroot -p123456 meteo sdata --where=&#34; sensorid=11 and fieldid=0&#34; &#62; /home/xyx/Temp.txt</span><br></pre></td></tr></table></figure>
<h2 id="u5B58_u50A8_u8FC7_u7A0B"><a href="#u5B58_u50A8_u8FC7_u7A0B" class="headerlink" title="存储过程"></a>存储过程</h2><figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br></pre></td><td class="code"><pre><span class="line">CREATE TABLE employees (</span><br><span class="line">    id INT NOT NULL,</span><br><span class="line">    fname VARCHAR(30),</span><br><span class="line">    lname VARCHAR(30),</span><br><span class="line">    hired DATE NOT NULL DEFAULT '1970-01-01',</span><br><span class="line">    separated DATE NOT NULL DEFAULT '9999-12-31',</span><br><span class="line">    job_code INT,</span><br><span class="line">    store_id INT</span><br><span class="line">);</span><br><span class="line">/*  </span><br><span class="line">在使用复合语句时，必须解决这样一个问题： 复合语句块里的语句必须以分号（;）彼此隔开，但分号同时也是mysql程序默认使用的语句分隔符，所以在使用mysql程序定义存储过程时会发生冲突。解决这个问题的办法是:使用delimiter命令把sql语句分隔符重定义为另一字符或字符串，且必须是在存储过程定义里没有出现过的。</span><br><span class="line">*/</span><br><span class="line">DELIMITER $$</span><br><span class="line">DROP PROCEDURE IF EXISTS SampleProc$$</span><br><span class="line">CREATE PROCEDURE SampleProc()</span><br><span class="line">    BEGIN</span><br><span class="line">        DECLARE x INT;</span><br><span class="line">        SET x = 1000;</span><br><span class="line">        WHILE x&lt;= 2000 DO</span><br><span class="line">            insert into employees(id,fname,lname,hired,separated,job_code,store_id) values(x,concat('firstname',x),concat('ai',x),'1994-01-01','1995-01-01',10,20);</span><br><span class="line">            SET  x = x + 1;</span><br><span class="line">        END WHILE;</span><br><span class="line">    END$$</span><br><span class="line">     </span><br><span class="line">/*执行存储过程*/</span><br><span class="line">call SampleProc() $$</span><br><span class="line">delimiter ;</span><br></pre></td></tr></table></figure>
<h4 id="u52A8_u6001_u5EFA_u8868"><a href="#u52A8_u6001_u5EFA_u8868" class="headerlink" title="动态建表"></a>动态建表</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 按messageId动态建表，存储其推送信息</span></span><br><span class="line">DELIMITER $$</span><br><span class="line"><span class="operator"><span class="keyword">DROP</span> <span class="keyword">PROCEDURE</span> <span class="keyword">IF</span> <span class="keyword">EXISTS</span> MessagePushInfoProcedure$$</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">PROCEDURE</span> MessagePushInfoProcedure(<span class="keyword">in</span> messageId <span class="built_in">varchar</span>(<span class="number">32</span>))</span><br><span class="line"><span class="keyword">BEGIN</span></span><br><span class="line">  <span class="keyword">declare</span> table_prefix <span class="built_in">varchar</span>(<span class="number">20</span>);</span></span><br><span class="line">  <span class="operator"><span class="keyword">set</span> table_prefix=<span class="string">'t_message_push_info_'</span>;</span></span><br><span class="line">  <span class="operator"><span class="keyword">set</span> @table_name = <span class="keyword">concat</span>(table_prefix, messageId);</span> </span><br><span class="line">  <span class="comment">-- set @dropSql = concat('DROP TABLE IF EXISTS ', @table_name);</span></span><br><span class="line">  <span class="operator"><span class="keyword">set</span> @createSql = <span class="keyword">concat</span>(<span class="string">'CREATE TABLE IF NOT EXISTS '</span>, @table_name, </span><br><span class="line">        <span class="string">'(id varchar(32) NOT NULL,</span><br><span class="line">           api_key varchar(45) DEFAULT NULL,</span><br><span class="line">           device_id varchar(32) DEFAULT NULL,</span><br><span class="line">           message_id varchar(32) DEFAULT NULL,</span><br><span class="line">           message_status int(11) DEFAULT NULL,</span><br><span class="line">           receive_time datetime DEFAULT NULL,</span><br><span class="line">           operate_type int(11) DEFAULT NULL,</span><br><span class="line">           operate_time datetime DEFAULT NULL,</span><br><span class="line">           PRIMARY KEY (id)</span><br><span class="line">         ) ENGINE=MyISAM DEFAULT CHARSET=utf8;'</span>);</span></span><br><span class="line"><span class="comment">-- prepare dropStmt from @dropSql;</span></span><br><span class="line"><span class="comment">-- execute dropStmt;</span></span><br><span class="line"><span class="operator"><span class="keyword">prepare</span> createStmt <span class="keyword">from</span> @createSql;</span></span><br><span class="line"><span class="operator"><span class="keyword">execute</span> createStmt;</span></span><br><span class="line"><span class="operator"><span class="keyword">END</span>$$</span><br><span class="line"><span class="comment">-- 执行存储过程</span></span><br><span class="line"><span class="comment">-- call MessagePushInfoProcedure('aaaa') $$</span></span><br><span class="line">delimiter ;</span></span><br></pre></td></tr></table></figure>
<h2 id="u65E5_u671F_u64CD_u4F5C"><a href="#u65E5_u671F_u64CD_u4F5C" class="headerlink" title="日期操作"></a>日期操作</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br></pre></td><td class="code"><pre><span class="line">#查询当前日期，输出'2015-02-02'</span><br><span class="line"><span class="operator"><span class="keyword">select</span> <span class="keyword">curdate</span>() <span class="keyword">from</span> dual;</span></span><br><span class="line"></span><br><span class="line">#查询7天前日期，输出'2015-01-26'</span><br><span class="line"><span class="operator"><span class="keyword">select</span> <span class="keyword">date_sub</span>(<span class="keyword">curdate</span>(), <span class="built_in">INTERVAL</span> <span class="number">7</span> <span class="keyword">DAY</span>) <span class="keyword">from</span> dual;</span></span><br><span class="line"></span><br><span class="line">#查询当前的年月 输出'201502'</span><br><span class="line"><span class="operator"><span class="keyword">select</span> <span class="keyword">date_format</span>(<span class="keyword">curdate</span>(), <span class="string">'%Y%m'</span>) <span class="keyword">from</span> dual;</span></span><br><span class="line"></span><br><span class="line">#获取TIMESTAMP类型字段(包括年月日时分秒)的日期部分，输出'2015-02-02' '2015-02-01'</span><br><span class="line"><span class="operator"><span class="keyword">select</span> <span class="built_in">date</span>(F_Time) <span class="keyword">from</span> T_TimeTest;</span></span><br><span class="line"></span><br><span class="line">#查询今天的记录</span><br><span class="line"><span class="operator"><span class="keyword">select</span> * <span class="keyword">from</span> T_TimeTest <span class="keyword">where</span> <span class="keyword">to_days</span>(F_Time) = <span class="keyword">to_days</span>(<span class="keyword">now</span>());</span></span><br><span class="line"></span><br><span class="line">#查询昨天的记录</span><br><span class="line">#now() 能获得当前的日期时间</span><br><span class="line">#current_timestamp、localtime、localtimestamp等日期时间函数等同于now()</span><br><span class="line">#鉴于now()函数简短易记，建议使用now()来替代上面列出的函数。</span><br><span class="line"><span class="operator"><span class="keyword">select</span> * <span class="keyword">from</span> T_TimeTest <span class="keyword">where</span> <span class="keyword">to_days</span>(<span class="keyword">now</span>()) - <span class="keyword">to_days</span>(F_Time) = <span class="number">1</span>;</span></span><br><span class="line">#基本等价于下面的语句，但是如果有日期在当前日期之后（未来日期），该记录也会输出</span><br><span class="line"><span class="operator"><span class="keyword">select</span> * <span class="keyword">from</span> T_TimeTest <span class="keyword">where</span> <span class="keyword">date_sub</span>(<span class="keyword">curdate</span>(), <span class="built_in">INTERVAL</span> <span class="number">1</span> <span class="keyword">DAY</span>) &lt;= <span class="built_in">date</span>(F_Time);</span></span><br><span class="line"></span><br><span class="line">#查询最近7天的记录，也会输出未来日期</span><br><span class="line"><span class="operator"><span class="keyword">select</span> * <span class="keyword">from</span> T_TimeTest <span class="keyword">where</span> <span class="keyword">date_sub</span>(<span class="keyword">curdate</span>(), <span class="built_in">INTERVAL</span> <span class="number">7</span> <span class="keyword">DAY</span>) &lt;= <span class="built_in">date</span>(F_Time);</span></span><br><span class="line"></span><br><span class="line">#查询本月的信息记录：</span><br><span class="line"><span class="operator"><span class="keyword">select</span> * <span class="keyword">from</span> T_TimeTest <span class="keyword">where</span> <span class="keyword">date_format</span>(F_Time, <span class="string">'%Y%m'</span>) = <span class="keyword">date_format</span>(<span class="keyword">curdate</span>() , <span class="string">'%Y%m'</span>);</span></span><br><span class="line"></span><br><span class="line">#查询上一月的信息记录：</span><br><span class="line"><span class="operator"><span class="keyword">select</span> * <span class="keyword">from</span> T_TimeTest <span class="keyword">where</span> <span class="keyword">period_diff</span>(<span class="keyword">date_format</span>(<span class="keyword">now</span>() , <span class="string">'%Y%m'</span>) , <span class="keyword">date_format</span>(F_Time, <span class="string">'%Y%m'</span>)) = <span class="number">1</span>;</span></span><br></pre></td></tr></table></figure>
<p>　　<strong>to_days</strong>：将具体的某一个日期或时间字符串转换到某一天所对应的unix时间戳，2015.1.22日与2015.1.23日的差别就是，转换之后的数增加了1，这个粒度的查询是比较粗糙的，可以采用细粒度的查询方法str_to_date。<br>　　注意：其不用于阳历出现(1582)前的值，原因是当日历改变时，遗失的日期不会被考虑在内。因此对于1582年之前的日期(或许在其它地区为下一年 )，该函数的结果实不可靠的。MySQL”日期和时间类型”中的规则是将日期中的二位数年份值转化为四位。因此对于’1997-10-07’和’97-10-07’将被视为同样的日期:<br>　　<strong>str_to_date</strong>：把字符串时间完全的翻译过来<br><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 输出2010-11-23 14:39:51</span></span><br><span class="line"><span class="operator"><span class="keyword">select</span> <span class="keyword">str_to_date</span>(<span class="string">"2010-11-23 14:39:51"</span>,<span class="string">'%Y-%m-%d %H:%i:%s'</span>);</span></span><br><span class="line"><span class="operator"><span class="keyword">select</span> <span class="keyword">str_to_date</span>(article.<span class="string">`add_time`</span>,<span class="string">'%Y-%m-%d %H:%i:%s'</span>)</span><br><span class="line">    <span class="keyword">from</span> article  </span><br><span class="line">    <span class="keyword">where</span> <span class="keyword">str_to_date</span>(article.<span class="string">`add_time`</span>,<span class="string">'%Y-%m-%d %H:%i:%s'</span>)&gt;=<span class="string">'2012-06-28 08:00:00'</span> <span class="keyword">and</span> <span class="keyword">str_to_date</span>(article.<span class="string">`add_time`</span>,<span class="string">'%Y-%m-%d %H:%i:%s'</span>)&lt;=<span class="string">'2012-06-28 09:59:59'</span>;</span></span><br></pre></td></tr></table></figure></p>
<h2 id="u5B9A_u65F6_u4EFB_u52A1"><a href="#u5B9A_u65F6_u4EFB_u52A1" class="headerlink" title="定时任务"></a>定时任务</h2><p>　　自MySQL5.1.6起，增加了事件调度器(Event Scheduler)，用做定时任务执行（例如：删除记录等等），可以精确到每秒钟执行一个任务。事件调度器也可称为临时触发器(temporal triggers)，因为事件调度器是基于特定时间周期触发来执行某些任务，而触发器(Triggers)是基于某个表所产生的事件触发的，区别也就在这里。</p>
<h4 id="u5F00_u542Fevent_scheduler"><a href="#u5F00_u542Fevent_scheduler" class="headerlink" title="开启event_scheduler"></a>开启event_scheduler</h4><p>　　1. SET GLOBAL event_scheduler = 1; – mysql服务重启后失效<br>　　2. 在配置my.cnf文件中加上 event_scheduler = 1<br>　　3. SET GLOBAL event_scheduler = ON;<br>　　4. 在启动命令加上“–event_scheduler=1”，例如：mysqld … –event_scheduler=1</p>
<h4 id="u67E5_u770Bevent_scheduler_u72B6_u6001"><a href="#u67E5_u770Bevent_scheduler_u72B6_u6001" class="headerlink" title="查看event_scheduler状态"></a>查看event_scheduler状态</h4><p>　　1. SHOW VARIABLES LIKE ‘event_scheduler’;<br>　　2. SELECT @@event_scheduler;</p>
<h4 id="u521B_u5EFA_u4E8B_u4EF6"><a href="#u521B_u5EFA_u4E8B_u4EF6" class="headerlink" title="创建事件"></a>创建事件</h4><figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br></pre></td><td class="code"><pre><span class="line">-- 示例，每秒插入一条记录到数据表</span><br><span class="line">CREATE TABLE aaa (timeline TIMESTAMP);</span><br><span class="line"> </span><br><span class="line">CREATE EVENT e_test_insert</span><br><span class="line">    ON SCHEDULE EVERY 1 SECOND</span><br><span class="line">    DO INSERT INTO aaa VALUES(CURRENT_TIMESTAMP);</span><br><span class="line"></span><br><span class="line">-- 5天后清空test表</span><br><span class="line">CREATE EVENT e_test</span><br><span class="line">    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 DAY</span><br><span class="line">    DO TRUNCATE TABLE test.aaa;</span><br><span class="line"></span><br><span class="line">-- 2007年7月20日12点整清空test表</span><br><span class="line">CREATE EVENT e_test</span><br><span class="line">    ON SCHEDULE AT TIMESTAMP '2007-07-20 12:00:00'</span><br><span class="line">    DO TRUNCATE TABLE test.aaa;</span><br><span class="line"></span><br><span class="line">-- 每天定时清空test表</span><br><span class="line">CREATE EVENT e_test</span><br><span class="line">    ON SCHEDULE EVERY 1 DAY</span><br><span class="line">    DO TRUNCATE TABLE test.aaa;</span><br><span class="line"></span><br><span class="line">-- 5天后开启每天定时清空test表</span><br><span class="line">CREATE EVENT e_test</span><br><span class="line">    ON SCHEDULE EVERY 1 DAY</span><br><span class="line">    STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY</span><br><span class="line">    DO TRUNCATE TABLE test.aaa;</span><br><span class="line"></span><br><span class="line">-- 每天定时清空test表，5天后停止执行</span><br><span class="line">CREATE EVENT e_test</span><br><span class="line">    ON SCHEDULE EVERY 1 DAY</span><br><span class="line">    ENDS CURRENT_TIMESTAMP+ INTERVAL 5 DAY</span><br><span class="line">    DO TRUNCATE TABLE test.aaa;</span><br><span class="line"></span><br><span class="line">-- 5天后开启每天定时清空test表，一个月后停止执行</span><br><span class="line">CREATE EVENT e_test</span><br><span class="line">    ON SCHEDULE EVERY 1 DAY</span><br><span class="line">    STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY</span><br><span class="line">    ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH</span><br><span class="line">    DO TRUNCATE TABLE test.aaa;</span><br><span class="line"></span><br><span class="line">-- 每天定时清空test表(只执行一次，任务完成后就终止该事件</span><br><span class="line">CREATE EVENT e_test</span><br><span class="line">    ON SCHEDULE EVERY 1 DAY</span><br><span class="line">    ON COMPLETION NOT PRESERVE</span><br><span class="line">    DO TRUNCATE TABLE test.aaa;</span><br><span class="line"></span><br><span class="line">-- 临时关闭事件</span><br><span class="line">ALTER EVENT e_test DISABLE;</span><br><span class="line"></span><br><span class="line">-- 开启事件</span><br><span class="line">ALTER EVENT e_test ENABLE;</span><br><span class="line"></span><br><span class="line">-- 将每天清空test表改为5天清空一次</span><br><span class="line">ALTER EVENT e_test</span><br><span class="line">    ON SCHEDULE EVERY 5 DAY;</span><br><span class="line"></span><br><span class="line">-- 定时执行存储过程</span><br><span class="line">CREATE TABLE employees (</span><br><span class="line">    id INT NOT NULL primary key auto_increment,</span><br><span class="line">    fname VARCHAR(30)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line">DELIMITER $$</span><br><span class="line">DROP PROCEDURE IF EXISTS SampleProc$$</span><br><span class="line">CREATE PROCEDURE SampleProc()</span><br><span class="line">    BEGIN</span><br><span class="line">        DECLARE x INT;</span><br><span class="line">        SET x = 1;</span><br><span class="line">        WHILE x&lt;= 2 DO</span><br><span class="line">            insert into employees(fname) values(concat('firstname',x));</span><br><span class="line">            SET  x = x + 1;</span><br><span class="line">        END WHILE;</span><br><span class="line">    END$$</span><br><span class="line">/*执行存储过程*/</span><br><span class="line">-- call SampleProc() $$</span><br><span class="line">delimiter ;</span><br><span class="line"> </span><br><span class="line">CREATE EVENT e_test_insert</span><br><span class="line">    ON SCHEDULE EVERY 5 SECOND</span><br><span class="line">    DO call SampleProc();</span><br><span class="line"></span><br><span class="line">-- 利用 event scheduler 的特性，每秒钟调用一次存储过程，</span><br><span class="line">-- 用于判断SLAVE是否正常运行，如果发现 SLAVE 关闭了，忽略0次错误，然后重新启动SLAVE</span><br><span class="line">delimiter //</span><br><span class="line">create  procedure Slave_Monitor()</span><br><span class="line">begin</span><br><span class="line">    SELECT VARIABLE_VALUE INTO @SLAVE_STATUS </span><br><span class="line">    FROM information_schema.GLOBAL_STATUS</span><br><span class="line">    WHERE VARIABLE_NAME = 'SLAVE_RUNNING';</span><br><span class="line">    IF ('ON'!= @SLAVE_STATUS) THEN</span><br><span class="line">        SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 0;</span><br><span class="line">        SLAVE START;</span><br><span class="line">    END IF;</span><br><span class="line">end //</span><br><span class="line">delimiter ;</span><br><span class="line"> </span><br><span class="line">-- 由于存储过程中无法调用类似 SHOW SLAVE STATUS 这样的语句，</span><br><span class="line">-- 因此无法得到确切的复制错误信息和错误代码，</span><br><span class="line">-- 不能进一步的处理 SLAVE 停止的各种情况。</span><br><span class="line"> </span><br><span class="line">CREATE EVENT IF NOT EXISTS `Slave_Monitor`</span><br><span class="line">    ON SCHEDULE EVERY 5 SECOND</span><br><span class="line">    ON COMPLETION PRESERVE</span><br><span class="line">    DO CALL Slave_Monitor();</span><br><span class="line"> </span><br><span class="line">alter event `Slave_Monitor` ON COMPLETION PRESERVE ENABLE;</span><br><span class="line">alter event `Slave_Monitor` ON COMPLETION PRESERVE DISABLE;</span><br></pre></td></tr></table></figure>
<h2 id="u53C2_u8003_u94FE_u63A5"><a href="#u53C2_u8003_u94FE_u63A5" class="headerlink" title="参考链接"></a>参考链接</h2><p><a href="http://blog.csdn.net/xin_yu_xin/article/details/7574662" target="_blank" rel="external">http://blog.csdn.net/xin_yu_xin/article/details/7574662</a></p>

      
    </div>
    <footer class="article-footer">
      <a data-url="http://yoursite.com/2016/01/22/database/7.mysql使用笔记/" data-id="cijzzpvh700gb1cfs8ymb8txo" class="article-share-link">Share</a>
      
      
  <ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/database/">database</a></li></ul>

    </footer>
  </div>
  
</article>


  
    <article id="post-database/6.mysql编译安装" class="article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    <a href="/2016/01/22/database/6.mysql编译安装/" class="article-date">
  <time datetime="2016-01-22T08:31:00.000Z" itemprop="datePublished">2016-01-22</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/categories/database/">database</a>
  </div>

  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 itemprop="name">
      <a class="article-title" href="/2016/01/22/database/6.mysql编译安装/">mysql编译安装</a>
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <h2 id="u7B80_u4ECB"><a href="#u7B80_u4ECB" class="headerlink" title="简介"></a>简介</h2><p>　　本文基于centos7 + mysql-5.6.24进行编译安装</p>
<h2 id="u5B89_u88C5_u4F9D_u8D56_u5305"><a href="#u5B89_u88C5_u4F9D_u8D56_u5305" class="headerlink" title="安装依赖包"></a>安装依赖包</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">yum -y install cmake make ncurses-devel gcc gcc-c++ bison perl perl-Module-Install.noarch</span><br></pre></td></tr></table></figure>
<h2 id="u521B_u5EFAMySQL_u7528_u6237_u548C_u7528_u6237_u7EC4"><a href="#u521B_u5EFAMySQL_u7528_u6237_u548C_u7528_u6237_u7EC4" class="headerlink" title="创建MySQL用户和用户组"></a>创建MySQL用户和用户组</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">/usr/sbin/groupadd mysql</span><br><span class="line">/usr/sbin/useradd -g mysql mysql</span><br></pre></td></tr></table></figure>
<h2 id="u89E3_u538B_u7F29"><a href="#u89E3_u538B_u7F29" class="headerlink" title="解压缩"></a>解压缩</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">tar zxvf mysql-<span class="number">5.6</span>.<span class="number">12</span>.tar.gz</span><br><span class="line"><span class="built_in">cd</span> mysql-<span class="number">5.6</span>.<span class="number">12</span></span><br></pre></td></tr></table></figure>
<h2 id="cmake_u914D_u7F6E"><a href="#cmake_u914D_u7F6E" class="headerlink" title="cmake配置"></a>cmake配置</h2><p>　　MySQL5.5之前的版本是configure现在是cmake，格式略有不同，以下使用了换行符，如果不用换行符则在同一行。<br><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">cmake . -DCMAKE_INSTALL_PREFIX=/usr/<span class="built_in">local</span>/mysql \</span><br><span class="line">-DSYSCONFDIR=/usr/<span class="built_in">local</span>/mysql \</span><br><span class="line">-DMYSQL_DATADIR=/data/mysql/<span class="number">3306</span>/data \</span><br><span class="line">-DEXTRA_CHARSETS=all \</span><br><span class="line">-DDEFAULT_CHARSET=utf8 \</span><br><span class="line">-DDEFAULT_COLLATION=utf8_general_ci \</span><br><span class="line">-DENABLED_LOCAL_INFILE=<span class="number">1</span> \</span><br><span class="line">-DWITH_MYISAM_STORAGE_ENGINE=<span class="number">1</span> \</span><br><span class="line">-DWITH_INNOBASE_STORAGE_ENGINE=<span class="number">1</span> \</span><br><span class="line">-DWITH_MEMORY_STORAGE_ENGINE=<span class="number">1</span> \</span><br><span class="line">-DWITH_ARCHIVE_STORAGE_ENGINE=<span class="number">1</span> \</span><br><span class="line">-DWITH_BLACKHOLE_STORAGE_ENGINE=<span class="number">1</span> \</span><br><span class="line">-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \</span><br><span class="line">-DMYSQL_USER=mysql</span><br></pre></td></tr></table></figure></p>
<h2 id="u7F16_u8BD1_u5B89_u88C5"><a href="#u7F16_u8BD1_u5B89_u88C5" class="headerlink" title="编译安装"></a>编译安装</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">make</span><br><span class="line">make install</span><br></pre></td></tr></table></figure>
<h2 id="u914D_u7F6E"><a href="#u914D_u7F6E" class="headerlink" title="配置"></a>配置</h2><p>　　更改目录所有者为mysql并创建相关目录<br><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">chown -R mysql:mysql /usr/<span class="built_in">local</span>/mysql</span><br><span class="line">mkdir -p /data/mysql/<span class="number">3306</span>/data/</span><br><span class="line">mkdir -p /data/mysql/<span class="number">3306</span>/binlog/</span><br><span class="line">mkdir -p /data/mysql/<span class="number">3306</span>/relaylog/</span><br><span class="line">chown -R mysql:mysql /data/mysql/</span><br></pre></td></tr></table></figure></p>
<h2 id="u521D_u59CB_u5316"><a href="#u521D_u59CB_u5316" class="headerlink" title="初始化"></a>初始化</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">/usr/<span class="built_in">local</span>/mysql/scripts/mysql_install_db --basedir=/usr/<span class="built_in">local</span>/mysql \</span><br><span class="line">--datadir=/data/mysql/<span class="number">3306</span>/data --user=mysql --defaults-file=/usr/<span class="built_in">local</span>/mysql/my.cnf \</span><br><span class="line">--explicit_defaults_<span class="keyword">for</span>_timestamp</span><br><span class="line"><span class="comment">#此时会提示perl相关的错误，执行以下命令即可解决</span></span><br><span class="line">yum install -y perl-Module-Install.noarch</span><br></pre></td></tr></table></figure>
<h2 id="u5F00_u673A_u542F_u52A8"><a href="#u5F00_u673A_u542F_u52A8" class="headerlink" title="开机启动"></a>开机启动</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#删除默认配置文件</span></span><br><span class="line">rm -rf /etc/my.cnf</span><br><span class="line"><span class="built_in">cd</span> support-files/</span><br><span class="line">cp mysql.server /etc/init.d/mysqld</span><br><span class="line">chmod +x /etc/init.d/mysqld</span><br><span class="line">chkconfig --add mysqld</span><br><span class="line">chkconfig mysqld on</span><br></pre></td></tr></table></figure>
<h2 id="u542F_u52A8"><a href="#u542F_u52A8" class="headerlink" title="启动"></a>启动</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">service mysqld start</span><br></pre></td></tr></table></figure>
<h2 id="u767B_u5F55"><a href="#u767B_u5F55" class="headerlink" title="登录"></a>登录</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#登陆mysql</span></span><br><span class="line">/usr/<span class="built_in">local</span>/mysql/bin/mysql</span><br><span class="line"><span class="comment">#创建root用户，并将密码也设置成root</span></span><br><span class="line">GRANT ALL PRIVILEGES ON *.* TO <span class="string">'root'</span>@<span class="string">'localhost'</span> IDENTIFIED BY <span class="string">'root'</span>;</span><br><span class="line">GRANT ALL PRIVILEGES ON *.* TO <span class="string">'root'</span>@<span class="string">'127.0.0.1'</span> IDENTIFIED BY <span class="string">'root'</span>;</span><br><span class="line">flush privileges;</span><br></pre></td></tr></table></figure>
<h2 id="u6743_u9650"><a href="#u6743_u9650" class="headerlink" title="权限"></a>权限</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="operator"><span class="keyword">grant</span> all <span class="keyword">privileges</span> <span class="keyword">on</span> *.* <span class="keyword">to</span> joe@localhost <span class="keyword">identified</span> <span class="keyword">by</span> <span class="string">'1'</span>;</span></span><br><span class="line"><span class="operator"><span class="keyword">flush</span> <span class="keyword">privileges</span>;</span></span><br></pre></td></tr></table></figure>
<p>　　grant 权限1,权限2,…权限n on 数据库名称.表名称 to 用户名@用户地址 identified by‘连接口令’;<br>　　权限包括：select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等14个权限。<br>　　当权限1,权限2,…权限n被all privileges或者all代替，表示赋予用户全部权限。<br>　　当数据库名称.表名称被<em>.</em>代替，表示赋予用户操作服务器上所有数据库所有表的权限。<br>　　用户地址可以是localhost，也可以是ip地址、机器名字、域名。也可以<strong>用’%’表示从任何地址连接</strong>。<br>　　“连接口令”不能为空，否则创建失败。</p>
<h2 id="u4F18_u5316"><a href="#u4F18_u5316" class="headerlink" title="优化"></a>优化</h2><p>　　编辑MySQL的配置文件/usr/local/mysql/my.cnf<br><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#错误日志目录</span></span><br><span class="line"><span class="built_in">log</span>-error = /data/mysql/<span class="number">3306</span>/mysql_error.log</span><br><span class="line"><span class="comment">#二进制日志目录，replication复制需要开启</span></span><br><span class="line"><span class="built_in">log</span>-bin = /data/mysql/<span class="number">3306</span>/binlog/binlog.log</span><br><span class="line"></span><br><span class="line"><span class="comment">#InnoDB 缓存数据大小(索引、锁、插入缓冲、数据字典等)</span></span><br><span class="line"><span class="comment">#推荐设置为物理内存的25%-50%</span></span><br><span class="line">innodb_buffer_pool_size = <span class="number">1</span>G</span><br><span class="line"></span><br><span class="line"><span class="comment">#InnoDB共享表空间初始化大小</span></span><br><span class="line">innodb_data_file_path = ibdata1:<span class="number">1</span>G:autoextend</span><br><span class="line"></span><br><span class="line"><span class="comment">#每次事务提交或事务外的指令都需要将日志写入硬盘，设置为1比较安全</span></span><br><span class="line">innodb_flush_<span class="built_in">log</span>_at_trx_commit = <span class="number">1</span></span><br><span class="line"></span><br><span class="line"><span class="comment">#InnoDB日志缓存</span></span><br><span class="line">innodb_<span class="built_in">log</span>_buffer_size = <span class="number">64</span>M</span><br><span class="line"></span><br><span class="line"><span class="comment">#InnoDB 大量写操作时有帮助</span></span><br><span class="line">innodb_<span class="built_in">log</span>_file_size = <span class="number">256</span>M</span><br><span class="line"></span><br><span class="line"><span class="comment">#启用InnoDB的status file</span></span><br><span class="line">innodb_status_file = <span class="number">1</span></span><br><span class="line"></span><br><span class="line"><span class="comment">#最大连接数</span></span><br><span class="line">max_connections = <span class="number">100</span></span><br><span class="line"></span><br><span class="line"><span class="comment">#最大连接错误次数</span></span><br><span class="line">max_connect_errors = <span class="number">10000</span></span><br><span class="line"></span><br><span class="line"><span class="comment">#设置慢查询时间</span></span><br><span class="line">long_query_time = <span class="number">1</span></span><br><span class="line"></span><br><span class="line"><span class="comment">#设置临时表最大值，max_heap_table_size 和 tmp_table_size 要设置一样大</span></span><br><span class="line">max_heap_table_size = <span class="number">96</span>M</span><br><span class="line">tmp_table_size = <span class="number">96</span>M</span><br></pre></td></tr></table></figure></p>
<h2 id="u53C2_u8003_u94FE_u63A5"><a href="#u53C2_u8003_u94FE_u63A5" class="headerlink" title="参考链接"></a>参考链接</h2>
      
    </div>
    <footer class="article-footer">
      <a data-url="http://yoursite.com/2016/01/22/database/6.mysql编译安装/" data-id="cijzzpvha00ge1cfsuqkd87eq" class="article-share-link">Share</a>
      
      
  <ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/database/">database</a></li></ul>

    </footer>
  </div>
  
</article>


  
    <article id="post-database/5.mysql集群" class="article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    <a href="/2016/01/22/database/5.mysql集群/" class="article-date">
  <time datetime="2016-01-22T06:23:00.000Z" itemprop="datePublished">2016-01-22</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/categories/database/">database</a>
  </div>

  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 itemprop="name">
      <a class="article-title" href="/2016/01/22/database/5.mysql集群/">mysql集群</a>
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <h2 id="Replication_u539F_u7406"><a href="#Replication_u539F_u7406" class="headerlink" title="Replication原理"></a>Replication原理</h2><p>　　首先打开Master端的Binary Log功能（通过在启动命令中使用“—log-bin” 参数，或者在my.cnf配置文件中的mysqld参数组（[mysqld]标识后的参数部分）增加“log-bin” 参数项。）<br>　　在Master与Slave之间，整个复制过程主要由三个线程来完成，其中两个线程（Sql线程和IO线程）在Slave 端， 另外一个线程（IO线程）在Master 端。 </p>
<h2 id="u590D_u5236_u8FC7_u7A0B"><a href="#u590D_u5236_u8FC7_u7A0B" class="headerlink" title="复制过程"></a>复制过程</h2><p>　　1. Slave的IO线程连接上Master后，请求从指定日志文件的指定位置之后的日志内容；<br>　　2. Master收到Slave的IO线程的请求后，通过负责复制的IO线程<strong>根据请求信息读取指定日志指定位置之后的日志信息</strong>，返回给Slave 端的IO线程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息在Master端的Binary Log文件的名称以及在Binary Log 中的位置；<br>　　3. Slave的IO线程接收到信息后，将接收到的日志内容依次写入到Slave端的Relay Log文件(mysql-relay-bin.xxxxxx)的最末端，并将读取到的Master端的binlog的文件名和位置记录到master-info文件中。<br>　　4. Slave的SQL线程检测到Relay Log中新增加了内容后，会解析该Log文件中的内容，还原成在Master端执行时的那些Query 语句，并在自身执行。这样，实际上就是在Master端和Slave端执行了同样的Query，所以两端的数据是完全一样的。<br>　　由于复制是异步的，只要数据的更改不是在一个事务中，都可能导致数据丢失（master挂了，但是slave还有部分数据没有同步）</p>
<h2 id="Replication_u7EA7_u522B"><a href="#Replication_u7EA7_u522B" class="headerlink" title="Replication级别"></a>Replication级别</h2><p>　　基于语句（Statement Level）、基于记录（Row level）和Mixed Level（前两种模式的结合）三种模式，可以在MySQL 的配置参数中设置，不同的设置会影响Binary Log记录成不同的形式。 </p>
<h4 id="Row_Level"><a href="#Row_Level" class="headerlink" title="Row Level"></a>Row Level</h4><p>　　Binary Log中会记录成每条数据被修改的形式，然后在Slave端再对相同的数据进行修改。<br>　　优点：Binary Log中可以不记录执行sql语句的上下文相关的信息，只记录哪条记录被修改成什么样了。所以Row Level的日志 内容会记录下每条数据被修改的细节。不会出现某些特定情况，比如存储过程、function和trigger的调用和触发无法被正确复制的问题。<br>　　缺点：一条sql，比如update可能会产生大量的日志内容（这条语句更新的每条记录的变化情况单独记录）自然，Binary Log日志的量就会很大。尤其是当执行ALTER TABLE 之类的语句的时候。因为MySQL对于ALTER TABLE之类的DDL的处理方式是<strong>重建整个表的所有数据，也就是说表中的每一条记录都需要变动</strong>，那么该表的每一条记录都会被记录到日志中。</p>
<h4 id="Statement_Level"><a href="#Statement_Level" class="headerlink" title="Statement Level"></a>Statement Level</h4><p>　　每一条修改数据的Query都会记录到Binary Log中。Slave在复制的时候SQL线程会解析成和原来Master端执行过的相同的Query来再次执行。<br>　　优点：解决了Row Level的缺点，不需要记录每条数据的变化，减少Binary Log日志量，节约了IO成本。因为只需要记录在Master上执行的语句的细节和上下文的信息。<br>　　缺点：在使用某些特定的函数或功能时会出现问题，比如：sleep()函数在有些版本中就不能真确复制，在存储过程中使用了last_insert_id()函数，可能会使slave和master上得到不一致的id等等。由于row level是基于每一行来记录的变化，所以不会出现类似的问题。</p>
<h4 id="Mixed_Level"><a href="#Mixed_Level" class="headerlink" title="Mixed Level"></a>Mixed Level</h4><p>　　在Mixed模式下，MySQL根据执行的每一条具体的Query语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。新版本的Row Level模式并不是所有的修改都会以Row Level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果Query语句确实就是UPDATE 或者DELETE 等修改数据的语句，那么还是会记录所有行的变更。</p>
<h2 id="master-slave"><a href="#master-slave" class="headerlink" title="master-slave"></a>master-slave</h2><p>　　支持一主多从的配置。在Master端并不Care有多少个Slave连上了，只要有Slave的IO线程通过了连接认证，并请求指定位置之后的Binary Log信息，就会按照该IO线程的要求，读取自己的Binary Log信息，返回给Slave的IO线程。<br>　　注意：暂时还不支持一个Slave节点从多个Master节点来进行复制的架构，主要是为了避免冲突的问题，防止多个数据源之间的数据出现冲突，而造成最后数据的不一致性。</p>
<h2 id="master-master_u914D_u7F6E"><a href="#master-master_u914D_u7F6E" class="headerlink" title="master-master配置"></a>master-master配置</h2><p>　　1. 打开Master端Binary Log选项：在my.cnf配置文件中配置log-bin[=path for binary log]参数，在启动MySQL时使用–log-bin选项。<br>　　2. 创建一个全新的专用于复制的帐户，或者通过给一个现有帐户授予复制相关的权限。实现MySQL Replication仅仅只需要“REPLICATION SLAVE”权限即可。可以通过如下方式来创建这个用户：<br><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="operator"><span class="keyword">CREATE</span> <span class="keyword">USER</span> <span class="string">'repl'</span>@<span class="string">'192.168.0.2'</span> <span class="keyword">IDENTIFIED</span> <span class="keyword">BY</span> <span class="string">'password'</span>;</span></span><br><span class="line"><span class="operator"><span class="keyword">GRANT</span> <span class="keyword">REPLICATION</span> <span class="keyword">SLAVE</span> <span class="keyword">ON</span> *.* <span class="keyword">TO</span> <span class="string">'repl'</span>@<span class="string">'192.168.0.2'</span>;</span></span><br></pre></td></tr></table></figure></p>
<p>　　3. 配置Slave：是通过CHANGE MASTER TO 命令来配置然后再启动Slave。</p>
<h2 id="master-master"><a href="#master-master" class="headerlink" title="master-master"></a>master-master</h2><p>　　有时候要停止Master端。为了尽可能减少写服务的停机时间，最佳的做法就是将Slave节点切换成Master来提供写入的服务。当原Master又可以正常提供服务的时候，需要重新搭建Replication环境，并以原Master作为Slave来对外提供读的服务。这样操作工作量太大，也容易出现错误<br>　　为了解决这个问题，可以搭建Dual Master环境，实际上就是两个MySQL互相将对方作为自己的Master，自己作为对方的Slave来进行复制。<br>　　在Binary Log中记录了当前MySQL的server-id，这个参数也是搭建MySQL Replication时必须明确指定，<strong>Master和Slave的server-id参数值需要不一致</strong>，此时MySQL 就很容易判断某个变更是从哪一个MySQL产生的，避免出现循环复制的情况。如果我们不<strong>打开Slave的Binary Log的选项</strong>（–log-slave-update），MySQL就不会记录复制过程中的变更到Binary Log中，就更不用担心可能会出现循环复制的情形了。<br>　　Dual Master环境，并不是为了让两端都提供写的服务。而是将其中一端开启写服务，另一端只是提供读服务，或者完全不提供任何服务，仅仅只是作为一个备用的机器存在。这样可以避免数据的冲突，防止造成数据的不一致性。因为即使在两边执行的修改有先后顺序，但由于Replication是异步的实现机制，同样会导致即使晚做的修改也可能会被早做的修改所覆盖，如下所示：</p>
<table>
<thead>
<tr>
<th style="text-align:left">时间点</th>
<th style="text-align:left">MySQL A</th>
<th style="text-align:left">MySQL B</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align:left">1</td>
<td style="text-align:left">更新x表y记录为10</td>
<td style="text-align:left"></td>
</tr>
<tr>
<td style="text-align:left">2</td>
<td style="text-align:left"></td>
<td style="text-align:left">更新x表y记录为20</td>
</tr>
<tr>
<td style="text-align:left">3</td>
<td style="text-align:left"></td>
<td style="text-align:left">获取到A日志，更新x表的y记录为1 0（不符合期望）</td>
</tr>
<tr>
<td style="text-align:left">4</td>
<td style="text-align:left">获取B日志，更新x表y记录为20（符合期望）</td>
</tr>
</tbody>
</table>
<p>　　这种情形下，不仅在B库上面的数据不是用户所期望的结果，A 和B两边的数据也出现了不一致。 可以通过特殊的约定，让某些表的写操作全部在一端，而另外一些表的写操作全部在另外一端，保证两端不会操作相同的表，这样就能避免上面问题的发生了。</p>
<h2 id="master-slaves-slaves"><a href="#master-slaves-slaves" class="headerlink" title="master-slaves-slaves"></a>master-slaves-slaves</h2><p>　　当系统读压力特别大的时候，一个Master需要许多的Slave才能够支撑。当master写的压力变大的时候，由于它连上来的Slave IO线程较多，导致复制消耗更多的资源，造成复制的延时。<br>　　此时可以在Slave端记录复制所产生变更的Binary Log信息的功能，也就是<strong>打开—log-slave-update选项</strong>。然后，通过二级或者更多级别复制来减少Master 端因为复制所带来的压力。也就是说，首先通过少数几台MySQL(第一级Slave)从Master来进行复制，然后其他的Slave再从第一级Slave 集群来进行复制。这样很容易就控制了每一台MySQL上面所附属Slave的数量。这种架构称之为Master - Slaves - Slaves 架构，如下图所示：<img src="/images/master-slaves-slaves.png" alt="Master - Slaves - Slaves"><br>　　当遇到这种情况的时候，建议通过拆分成多个Replication集群来解决上述瓶颈问题。增加复制的级联层次，同一个变更传到最底层的Slave 所需要经过的MySQL也会更多，造成延时较长的风险。当然，分拆集群也需要更复杂的技术和更复杂的应用系统架构。</p>
<h2 id="master-master-slaves"><a href="#master-master-slaves" class="headerlink" title="master-master-slaves"></a>master-master-slaves</h2><p>　　和Master - Slaves - Slaves 架构相比，只是将第一级Slave集群换成了一台单独的Master，作为备用Master，然后再从这个备用的Master 进行复制到一个Slave 集群。下面的图片更清晰的展示了这个架构的组成：<img src="/images/master-master-slaves.png" alt="master-master-slaves"><br>　　这种Dual Master 与级联复制结合的架构，最大的好处就是既可以<strong>避免主Master的写入操作不会受到Slave集群的复制所带来的影响</strong>，同时主Master 需要切换的时候也基本上不会出现重搭Replication 的情况。</p>
<h2 id="u7ECF_u9A8C_u5206_u4EAB"><a href="#u7ECF_u9A8C_u5206_u4EAB" class="headerlink" title="经验分享"></a>经验分享</h2><p>　　1. 当MySQL主从复制在 show slave status时出现Slave_IO_Running或Slave_SQL_Running 的值不为YES时，需要首先通过 stop slave 来停止从服务器，然后再执行一次<br>　　2. 一主多从的架构并不是最好的架构，通常比较优的做法是通过程序代码和中间件等方面来规划，比如设置对表数据的自增id值差异增长等方式来实现两个或多个主服务器，但一定要注意保证好这些主服务器数据的完整性，否则效果会比多个一主多从的架构还要差；</p>
<h2 id="u53C2_u8003_u94FE_u63A5"><a href="#u53C2_u8003_u94FE_u63A5" class="headerlink" title="参考链接"></a>参考链接</h2><p><a href="http://blog.jobbole.com/89140/" target="_blank" rel="external">http://blog.jobbole.com/89140/</a><br>利用Haproxy给MySQL Cluster做负载均衡：<a href="http://www.cnblogs.com/wang1988ming/archive/2012/10/24/2737507.html" target="_blank" rel="external">http://www.cnblogs.com/wang1988ming/archive/2012/10/24/2737507.html</a></p>

      
    </div>
    <footer class="article-footer">
      <a data-url="http://yoursite.com/2016/01/22/database/5.mysql集群/" data-id="cijzzpvhd00gh1cfsobo41gzh" class="article-share-link">Share</a>
      
      
  <ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/database/">database</a></li></ul>

    </footer>
  </div>
  
</article>


  
    <article id="post-spring/5.spring综述" class="article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    <a href="/2016/01/22/spring/5.spring综述/" class="article-date">
  <time datetime="2016-01-22T02:40:00.000Z" itemprop="datePublished">2016-01-22</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/categories/spring/">spring</a>
  </div>

  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 itemprop="name">
      <a class="article-title" href="/2016/01/22/spring/5.spring综述/">spring综述</a>
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <h2 id="spring_u4F18_u52BF"><a href="#spring_u4F18_u52BF" class="headerlink" title="spring优势"></a>spring优势</h2><p>　　1、Spring能很好的与各大框架，如struts、mybatis等进行集成<br>　　2、其IOC机制（DI，控制反转、依赖注入）节省了大量手动创建和注入对象的代码量<br>　　3、其提供的AOP机制实现了事务的统一管理，同时也方便对已有业务功能的增强而不用修改原有代码，如事务管理、权限控制、日志记录、性能统计等。AOP的作用只是提供了一种更好的办法，用更少的工作量来解决现有的一些问题，使得系统更加健壮，可维护性更好。<br>　　4、实现了大量常用且实用的功能，比如：基于aop的cache管理，并且集成了常用的缓存框架。session管理，方便分布式系统实现统一的用登录管理。与各种消息队列集成的插件。<br>　　5、其提供的模板机制，如jdbcTemplate、redisTemplate等，基本上统一了数据操作代码风格，方便维护<br>　　6、推出的微服务框架spring boot进一步减少了配置量，加快了开发进度，并提供了与docker集成的maven插件，实现快速部署<br>　　7、</p>
<h2 id="SSH"><a href="#SSH" class="headerlink" title="SSH"></a>SSH</h2><p>　　Struts是一个表示层框架，主要作用是界面展示，接收请求，分发请求，相当于Spring MVC的功能<br>　　Hibernate是一个持久层框架，它只负责与关系数据库的操作。<br>　　Spring是一个业务层框架，是一个整合的框架，能够很好地黏合表示层与持久层。</p>
<h2 id="u4F9D_u8D56_u6CE8_u5165"><a href="#u4F9D_u8D56_u6CE8_u5165" class="headerlink" title="依赖注入"></a>依赖注入</h2><p>　　IOC是一种思想，方便设计出<strong>松耦合</strong>、更优良的程序。传统应用程序都是由我们在类内部主动创建依赖对象，从而导致类与类之间高耦合，难于测试；有了IOC容器后，把创建和查找依赖对象的控制权交给了容器，由容器进行注入组合对象，所以<strong>对象与对象之间是松散耦合</strong>，这样也方便测试，利于功能复用，更重要的是使得程序的整个体系结构变得非常灵活。所以依赖注入的最大作用就是：<strong>减少类间耦合度，避免用new来创建对象</strong></p>
<h2 id="BeanFactory_u4E0EApplicationContext"><a href="#BeanFactory_u4E0EApplicationContext" class="headerlink" title="BeanFactory与ApplicationContext"></a>BeanFactory与ApplicationContext</h2><p>　　1、BeanFactory负责读取bean的配置文件，管理bean的加载、实例化，维护bean之间的依赖关系，负责bean的生命周期。<br>　　2、ApplicationContext除了提供上述BeanFactory所能提供的功能之外，还提供了更完整的框架功能： 国际化支持、资源访问、事件传递</p>
<h2 id="Struts"><a href="#Struts" class="headerlink" title="Struts"></a>Struts</h2><p>　　1. 在Struts2框架中的处理大概分为以下几个步骤<br>http请求经过一系列的过滤器（Filter）后，FilterDispatcher被调用，询问ActionMapper是否有匹配的Action<br>　　2. 如果有、FilterDispatcher把请求的处理交给ActionProxy<br>　　3. ActionProxy通过Configuration Manager询问框架的配置文件，找到需要调用的Action类<br>　　4. ActionProxy<strong>创建一个ActionInvocation的实例</strong>。<br>　　5. ActionInvocation实例使用命名模式来调用，在调用Action的过程前后，涉及到相关拦截器（Intercepter）的调用。<br>　　6. Action执行完毕，ActionInvocation根据struts.xml中的配置找到对应的返回结果。返回结果通常是（也可 能是另外的一个Action链）一个需要被表示的JSP或者FreeMarker的模版。</p>
<h2 id="Hibernate_u7F13_u5B58"><a href="#Hibernate_u7F13_u5B58" class="headerlink" title="Hibernate缓存"></a>Hibernate缓存</h2><p>　　Hibernate缓存包括两大类：Session一级缓存和SessionFactory二级缓存。<br>　　Session的缓存：内置，不可以取消的。Session对象的生命周期通常对应一个数据库事务或者一个应用事务，因此它的缓存是事务范围的缓存。<strong>在一级缓存中，持久化类的每个实例都具有唯一的OID</strong>。如果tb事务提交或回滚，session就关闭了，生命周期结束了。缓存和连接池的区别：池是重量级的，里面的数据是一样的，缓存里的数据，每个都不一样。<br>　　SessionFactory缓存：SessionFactory对象的生命周期和应用程序的整个过程对应，因此Hibernate二级缓存是进程范围或者集群范围的缓存，有可能出现并发问题，因此需要采用适当的并发访问策略，该策略为被缓存的数据提供了事务隔离级别。第二级缓存是可选的，是一个可配置的插件，在默认情况下，不会启用这个插件。<br>　　当Hibernate根据ID访问数据对象的时候，首先从Session一级缓存中查；查不到，如果配置了二级缓存，那么从二级缓存中查；如果都查不到，再查询数据库，把结果按照ID放入到缓存。删除、更新、增加数据的时候，同时更新缓存</p>
<table>
<thead>
<tr>
<th style="text-align:left"></th>
<th style="text-align:left">一级缓存</th>
<th style="text-align:left">二级缓存</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align:left">存放数据的形式</td>
<td style="text-align:left">相互关联的持久化对象</td>
<td style="text-align:left">对象的散装数据</td>
</tr>
<tr>
<td style="text-align:left">缓存的范围</td>
<td style="text-align:left">事务范围，每个事务都拥有单独的一级缓存</td>
<td style="text-align:left">进程范围或集群范围，缓存被同一个进程或集群范围内所有事务共享</td>
</tr>
<tr>
<td style="text-align:left">并发访问策略</td>
<td style="text-align:left">由于每个事务都拥有单独的一级缓存不会出现并发问题，因此无须提供并发访问策略</td>
<td style="text-align:left">由于多个事务会同时访问二级缓存中的相同数据，因此必须提供适当的并发访问策略，来保证特定的事务隔离级别</td>
</tr>
<tr>
<td style="text-align:left">数据过期策略</td>
<td style="text-align:left">处于一级缓存中的对象永远不会过期，除非应用程序显示清空或者清空特定对象</td>
<td style="text-align:left">必须提供数据过期策略，如基于内存的缓存中对象的最大数目，允许对象处于缓存中的最长时间，以及允许对象处于缓存中的最长空闲时间</td>
</tr>
<tr>
<td style="text-align:left">物理介质</td>
<td style="text-align:left">内存</td>
<td style="text-align:left">内存和硬盘，对象的散装数据首先存放到基于内存的缓存中，当内存中对象的数目达到数据过期策略的maxElementsInMemory值，就会把其余的对象写入基于硬盘的缓存中</td>
</tr>
<tr>
<td style="text-align:left">缓存软件实现</td>
<td style="text-align:left">在Hibernate的Session的实现中包含</td>
<td style="text-align:left">由第三方提供，Hibernate仅提供了缓存适配器，用于把特定的缓存插件集成到Hibernate中</td>
</tr>
<tr>
<td style="text-align:left">启用缓存的方式</td>
<td style="text-align:left">只要通过Session接口来执行保存，更新，删除，加载，查询，Hibernate就会启用一级缓存，对于批量操作，如不希望启用一级缓存，直接通过JDBCAPI来执行</td>
<td style="text-align:left">用户可以再单个类或类的单个集合的粒度上配置第二级缓存，如果类的实例被经常读，但很少被修改，就可以考虑使用二级缓存，只有为某个类或集合配置了二级缓存，Hibernate在运行时才会把它的实例加入到二级缓存中</td>
</tr>
<tr>
<td style="text-align:left">用户管理缓存的方式</td>
<td style="text-align:left">一级缓存的物理介质为内存，由于内存的容量有限，必须通过恰当的检索策略和检索方式来限制加载对象的数目，Session的evit（）方法可以显示的清空缓存中特定对象，但不推荐</td>
<td style="text-align:left">二级缓存的物理介质可以使内存和硬盘，因此第二级缓存可以存放大容量的数据，数据过期策略的maxElementsInMemory属性可以控制内存中的对象数目，管理二级缓存主要包括两个方面：选择需要使用第二级缓存的持久化类，设置合适的并发访问策略；选择缓存适配器，设置合适的数据过期策略。SessionFactory的evit（）方法也可以显示的清空缓存中特定对象，但不推荐</td>
</tr>
</tbody>
</table>
<h2 id="Mybatis"><a href="#Mybatis" class="headerlink" title="Mybatis"></a>Mybatis</h2><p>　　Mybatis 的着力点在于 POJO 与SQL之间的映射关系。通过映射配置文件，将SQL所需的参数，以及返回的结果字段映射到指定POJO。 相对Hibernate“O/R”而言，iBATIS 是一种“Sql Mapping”的ORM实现。<br>　　Hibernate和MyBatis都有相应的代码生成工具。可以生成简单基本的DAO层方法。针对高级查询，Mybatis需要手动编写SQL语句，以及ResultMap。而Hibernate有良好的映射机制，开发者无需关心SQL的生成与结果映射，可以更专注于业务流程（其实非常复杂，掌握起来也不容易）。</p>
<h2 id="Mybatis_u4F18_u52BF"><a href="#Mybatis_u4F18_u52BF" class="headerlink" title="Mybatis优势"></a>Mybatis优势</h2><p>　　MyBatis可以进行更为细致的SQL优化，可以减少查询字段。容易掌握，而Hibernate门槛较高。</p>
<h2 id="Hibernate_u4F18_u52BF"><a href="#Hibernate_u4F18_u52BF" class="headerlink" title="Hibernate优势"></a>Hibernate优势</h2><p>　　Hibernate的DAO层开发比MyBatis简单，Mybatis需要维护SQL和结果映射。<br>　　Hibernate对对象的维护和缓存要比MyBatis好，对增删改查的对象的维护要方便。<br>　　Hibernate<strong>数据库移植性好</strong>，MyBatis的数据库移植性不好，不同的数据库需要写不同SQL。<br>　　Hibernate有更好的二级缓存机制，可以使用第三方缓存。MyBatis本身提供的缓存机制不佳。</p>
<h2 id="u53C2_u8003_u8D44_u6599"><a href="#u53C2_u8003_u8D44_u6599" class="headerlink" title="参考资料"></a>参考资料</h2><p><a href="http://www.cnblogs.com/jifeng/archive/2011/09/14/2176599.html" target="_blank" rel="external">http://www.cnblogs.com/jifeng/archive/2011/09/14/2176599.html</a></p>

      
    </div>
    <footer class="article-footer">
      <a data-url="http://yoursite.com/2016/01/22/spring/5.spring综述/" data-id="cijzzpv1800141cfschp37xuo" class="article-share-link">Share</a>
      
      
  <ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/java/">java</a></li><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/spring/">spring</a></li></ul>

    </footer>
  </div>
  
</article>


  
    <article id="post-spring/4.spring mvc工作原理" class="article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    <a href="/2016/01/22/spring/4.spring mvc工作原理/" class="article-date">
  <time datetime="2016-01-22T02:30:00.000Z" itemprop="datePublished">2016-01-22</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/categories/spring/">spring</a>
  </div>

  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 itemprop="name">
      <a class="article-title" href="/2016/01/22/spring/4.spring mvc工作原理/">spring mvc工作原理</a>
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <h2 id="u7B80_u4ECB"><a href="#u7B80_u4ECB" class="headerlink" title="简介"></a>简介</h2><p>　　现在工作中的项目基本上都舍弃了struts，开始采用spring mvc了，这里我们就谈谈spring mvc的工作原理：<br>　　1. 客户端请求提交到DispatcherServlet<br>　　2. 由DispatcherServlet控制器查询一个或多个HandlerMapping，找到处理请求的Controller<br>　　3. DispatcherServlet将请求提交到Controller<br>　　4. Controller调用业务逻辑处理后，返回ModelAndView<br>　　5. DispatcherServlet查询一个或多个ViewResoler视图解析器，找到ModelAndView指定的视图<br>　　6. 视图负责将结果显示到客户端<br><figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">　</span><br></pre></td></tr></table></figure></p>
<h2 id="u603B_u7ED3"><a href="#u603B_u7ED3" class="headerlink" title="总结"></a>总结</h2><p>　　</p>
<h2 id="u53C2_u8003_u8D44_u6599"><a href="#u53C2_u8003_u8D44_u6599" class="headerlink" title="参考资料"></a>参考资料</h2><p><a href="http://www.cnblogs.com/jifeng/archive/2011/09/14/2176599.html" target="_blank" rel="external">http://www.cnblogs.com/jifeng/archive/2011/09/14/2176599.html</a></p>

      
    </div>
    <footer class="article-footer">
      <a data-url="http://yoursite.com/2016/01/22/spring/4.spring mvc工作原理/" data-id="cijzzpv23001b1cfs0s7d7sdm" class="article-share-link">Share</a>
      
      
  <ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/java/">java</a></li><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/spring/">spring</a></li></ul>

    </footer>
  </div>
  
</article>


  
    <article id="post-nio/19.Mina系列五之IoSession" class="article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    <a href="/2016/01/21/nio/19.Mina系列五之IoSession/" class="article-date">
  <time datetime="2016-01-21T13:03:00.000Z" itemprop="datePublished">2016-01-21</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/categories/nio/">nio</a>
  </div>

  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 itemprop="name">
      <a class="article-title" href="/2016/01/21/nio/19.Mina系列五之IoSession/">Mina系列五之IoSession</a>
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <h2 id="u7B80_u4ECB"><a href="#u7B80_u4ECB" class="headerlink" title="简介"></a>简介</h2><p>　　IoSession是对底层连接的封装，一个IoSession对应于一个底层的IO连接。通过IoSession，可以获取当前连接相关的上下文信息，以及向远程peer发送数据。发送数据其实也是个异步的过程。发送的操作首先会逆向穿过IoFilterChain，到达IoService。但IoService上并不会直接调用底层IO接口来将数据发送出去，而是会将该次调用封装成一个WriteRequest，放入session的writeRequestQueue中，最后由IoProcessor线程统一调度flush出去。所以发送操作并不会引起上层调用线程的阻塞。<br>　　具体代码可以参考org.apache.mina.core.filterchain.DefaultIoFilterChain的内部类HeadFilter的filterWrite方法。<br>　　下面我们看看session的uml图，如下所示：<img src="/images/mina-session.jpg" alt="session-uml"></p>
<h2 id="u521D_u59CB_u5316"><a href="#u521D_u59CB_u5316" class="headerlink" title="初始化"></a>初始化</h2><p>　　之前我们提到过，每当服务端有新的连接时，都会新建一个IoSession，IoSession会有一个随着时间演变的状态：<br>　　1. 已连接：会话已被创建并可用<br>　　2. 闲置：会话在至少一段时间 (这段时间是可配的) 内没有处理任何请求<br>        读闲置：在一段时间内没有任何读操作<br>        写闲置：在一段时间内没有任何写操作<br>        同时闲置：在一段时间内既没有读操作也没有写操作<br>　　3. 关闭中：会话正在关闭中 (还有正在清空的消息，清理尚未结束)<br>　　4. 已关闭：会话现在已被关闭，没有其他方法可以将其恢复<br>　　mina提供的SessionConfig可以为我们配置配置参数，比如接收缓冲大小、发送缓冲大小、空闲时间和写超时时间。</p>
<h2 id="u7A7A_u95F2_u68C0_u6D4B_u673A_u5236"><a href="#u7A7A_u95F2_u68C0_u6D4B_u673A_u5236" class="headerlink" title="空闲检测机制"></a>空闲检测机制</h2><p>　　我们知道，由于移动网络情况的特殊性，有的时候服务端是没法检测到客户端掉线的。此时就需要通过定时向服务端发心跳的机制来检测客户端端是否在线。如果长时间没有收到客户端的心跳，则判断客户端已经掉线！那么mina是通过什么机制来判断的呢！<br>　　在之前的示例代码中，我们调用了acceptor.getSessionConfig().setIdleTime( IdleStatus.BOTH_IDLE, 10 );方法，设置了读写空闲时间，当超过这个时间后，就会执行IoHandler中的sessionIdle方法，下面我们看看mina的实现机制。</p>
<h2 id="IdleStatusChecker"><a href="#IdleStatusChecker" class="headerlink" title="IdleStatusChecker"></a>IdleStatusChecker</h2><p>　　首先看看类IdleStatusChecker的实现，顾名思义，就是连接空间状态监测。看它的注释，可以发现IdleStatusChecker仅仅是用于那些不能触发idle事件的service，比如VmPipe和SerialTransport。<br><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">IdleStatusChecker</span> </span>&#123;</span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">final</span> Set&lt;AbstractIoSession&gt; sessions = <span class="keyword">new</span> ConcurrentHashSet&lt;AbstractIoSession&gt;();</span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">final</span> NotifyingTask notifyingTask = <span class="keyword">new</span> NotifyingTask();</span><br><span class="line"></span><br><span class="line">    <span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">NotifyingTask</span> <span class="keyword">implements</span> <span class="title">Runnable</span> </span>&#123;</span><br><span class="line">        <span class="keyword">private</span> <span class="keyword">volatile</span> <span class="keyword">boolean</span> cancelled;</span><br><span class="line">        <span class="keyword">private</span> <span class="keyword">volatile</span> Thread thread;</span><br><span class="line">        NotifyingTask() &#123;&#125;</span><br><span class="line"></span><br><span class="line">        <span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">run</span><span class="params">()</span> </span>&#123;</span><br><span class="line">            thread = Thread.currentThread();</span><br><span class="line">            <span class="keyword">try</span> &#123;</span><br><span class="line">                <span class="keyword">while</span> (!cancelled) &#123;</span><br><span class="line">                    <span class="keyword">long</span> currentTime = System.currentTimeMillis();</span><br><span class="line">                    notifySessions(currentTime);</span><br><span class="line">                    <span class="keyword">try</span> &#123;</span><br><span class="line">                        Thread.sleep(<span class="number">1000</span>);</span><br><span class="line">                    &#125; <span class="keyword">catch</span> (InterruptedException e) &#123;&#125;</span><br><span class="line">                &#125;</span><br><span class="line">            &#125; <span class="keyword">finally</span> &#123;</span><br><span class="line">                thread = <span class="keyword">null</span>;</span><br><span class="line">            &#125;</span><br><span class="line">        &#125;</span><br><span class="line"></span><br><span class="line">        <span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">cancel</span><span class="params">()</span> </span>&#123;</span><br><span class="line">            cancelled = <span class="keyword">true</span>;</span><br><span class="line">            Thread thread = <span class="keyword">this</span>.thread;</span><br><span class="line">            <span class="keyword">if</span> (thread != <span class="keyword">null</span>) &#123;</span><br><span class="line">                thread.interrupt();</span><br><span class="line">            &#125;</span><br><span class="line">        &#125;</span><br><span class="line"></span><br><span class="line">        <span class="function"><span class="keyword">private</span> <span class="keyword">void</span> <span class="title">notifySessions</span><span class="params">(<span class="keyword">long</span> currentTime)</span> </span>&#123;</span><br><span class="line">            Iterator&lt;AbstractIoSession&gt; it = sessions.iterator();</span><br><span class="line">            <span class="keyword">while</span> (it.hasNext()) &#123;</span><br><span class="line">                AbstractIoSession session = it.next();</span><br><span class="line">                <span class="keyword">if</span> (session.isConnected()) &#123;</span><br><span class="line">                    AbstractIoSession.notifyIdleSession(session, currentTime);</span><br><span class="line">                &#125;</span><br><span class="line">            &#125;</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure></p>
<p>　　其内部维护了一个类NotifyingTask，循环检测所有的IoSession，根据当前时间和IoSession上次的读写时间进行判断，下面我们看看NIO的判断机制！</p>
<h2 id="NIO_u68C0_u6D4BIdle_u673A_u5236"><a href="#NIO_u68C0_u6D4BIdle_u673A_u5236" class="headerlink" title="NIO检测Idle机制"></a>NIO检测Idle机制</h2><p>　　之前我们提到过，是由AbstractPollingIoProcessor的内部类Processor来处理连接和I/O的。在其run方法中，每次循环检测selectorkey的状态时，都会调用notifyIdleSessions方法，进行空闲检测，我们看看notifyIdleSessions方法！<br><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="function"><span class="keyword">private</span> <span class="keyword">void</span> <span class="title">notifyIdleSessions</span><span class="params">(<span class="keyword">long</span> currentTime)</span> <span class="keyword">throws</span> Exception </span>&#123;</span><br><span class="line">    <span class="comment">// process idle sessions</span></span><br><span class="line">    <span class="keyword">if</span> (currentTime - lastIdleCheckTime &gt;= SELECT_TIMEOUT) &#123;</span><br><span class="line">        lastIdleCheckTime = currentTime;</span><br><span class="line">        AbstractIoSession.notifyIdleness(allSessions(), currentTime);</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure></p>
<p>　　可以看到，其内部调用了AbstractIoSession的notifyIdleness方法，之后的流程如下：<br><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br></pre></td><td class="code"><pre><span class="line"><span class="function"><span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">notifyIdleness</span><span class="params">(Iterator&lt;? extends IoSession&gt; sessions, <span class="keyword">long</span> currentTime)</span> </span>&#123;</span><br><span class="line">    IoSession s = <span class="keyword">null</span>;</span><br><span class="line">    <span class="keyword">while</span> (sessions.hasNext()) &#123;</span><br><span class="line">        s = sessions.next();</span><br><span class="line">        notifyIdleSession(s, currentTime);</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br><span class="line">	</span><br><span class="line"><span class="function"><span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">notifyIdleSession</span><span class="params">(IoSession session, <span class="keyword">long</span> currentTime)</span> </span>&#123;</span><br><span class="line">    notifyIdleSession0(session, currentTime, session.getConfig().getIdleTimeInMillis(IdleStatus.BOTH_IDLE),</span><br><span class="line">            IdleStatus.BOTH_IDLE, Math.max(session.getLastIoTime(), session.getLastIdleTime(IdleStatus.BOTH_IDLE)));</span><br><span class="line"></span><br><span class="line">    notifyIdleSession0(session, currentTime, session.getConfig().getIdleTimeInMillis(IdleStatus.READER_IDLE),</span><br><span class="line">            IdleStatus.READER_IDLE,</span><br><span class="line">            Math.max(session.getLastReadTime(), session.getLastIdleTime(IdleStatus.READER_IDLE)));</span><br><span class="line"></span><br><span class="line">    notifyIdleSession0(session, currentTime, session.getConfig().getIdleTimeInMillis(IdleStatus.WRITER_IDLE),</span><br><span class="line">            IdleStatus.WRITER_IDLE,</span><br><span class="line">            Math.max(session.getLastWriteTime(), session.getLastIdleTime(IdleStatus.WRITER_IDLE)));</span><br><span class="line"></span><br><span class="line">    notifyWriteTimeout(session, currentTime);</span><br><span class="line">&#125;</span><br><span class="line"></span><br><span class="line"><span class="function"><span class="keyword">private</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">notifyIdleSession0</span><span class="params">(IoSession session, <span class="keyword">long</span> currentTime, <span class="keyword">long</span> idleTime, IdleStatus status,</span><br><span class="line">        <span class="keyword">long</span> lastIoTime)</span> </span>&#123;</span><br><span class="line">    <span class="keyword">if</span> ((idleTime &gt; <span class="number">0</span>) &amp;&amp; (lastIoTime != <span class="number">0</span>) &amp;&amp; (currentTime - lastIoTime &gt;= idleTime)) &#123;</span><br><span class="line">        session.getFilterChain().fireSessionIdle(status);</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br><span class="line"></span><br><span class="line"><span class="function"><span class="keyword">private</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">notifyWriteTimeout</span><span class="params">(IoSession session, <span class="keyword">long</span> currentTime)</span> </span>&#123;</span><br><span class="line">    <span class="keyword">long</span> writeTimeout = session.getConfig().getWriteTimeoutInMillis();</span><br><span class="line">    <span class="keyword">if</span> ((writeTimeout &gt; <span class="number">0</span>) &amp;&amp; (currentTime - session.getLastWriteTime() &gt;= writeTimeout)</span><br><span class="line">            &amp;&amp; !session.getWriteRequestQueue().isEmpty(session)) &#123;</span><br><span class="line">        WriteRequest request = session.getCurrentWriteRequest();</span><br><span class="line">        <span class="keyword">if</span> (request != <span class="keyword">null</span>) &#123;</span><br><span class="line">            session.setCurrentWriteRequest(<span class="keyword">null</span>);</span><br><span class="line">            WriteTimeoutException cause = <span class="keyword">new</span> WriteTimeoutException(request);</span><br><span class="line">            request.getFuture().setException(cause);</span><br><span class="line">            session.getFilterChain().fireExceptionCaught(cause);</span><br><span class="line">            <span class="comment">// WriteException is an IOException, so we close the session.</span></span><br><span class="line">            session.close(<span class="keyword">true</span>);</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure></p>
<p>　　上面的流程就非常简单了，这里不做过多的介绍！空闲状态的检测与io读写状态的变化在一起玩成了！</p>
<h2 id="IoFuture"><a href="#IoFuture" class="headerlink" title="IoFuture"></a>IoFuture</h2><p>　　IoFuture通过IoFutureListener被IoSession绑定。在IoFuture的实现中，分别提供了读、写、连接、关闭的future，通过这四个future来实现异步操作。异步操作很重要的一部分就是对线程的控制，所以在IoFuture这个接口中，我们能很清楚的理清这几个方法：await、join。当然还有notify，但是notify没有必要写在接口中，它可以在程序里直接使用。<img src="/images/iofuture.jpg" alt="iofuture.jpg"><br>　　它里面提供了检测死锁的方法，有兴趣的可以看看</p>
<h2 id="IoBuffer"><a href="#IoBuffer" class="headerlink" title="IoBuffer"></a>IoBuffer</h2><p>　　Mina中IoBuffer是一个对ByteBuffer的replacement，同样是用作缓冲区，做内容的切换和承载的容器，为什么要用重新封装ByteBuffer。用过ByteBuffer的人可能经常会遇到BufferOverflowException这样的异常，原因是<strong>buffer在初始化allocate之后就不能再自动的改变大小了</strong>。另外一点，就是IoBuffer作为一个应用框架的工具，必然会提供比原生Buffer更便捷的方法，比如IoBuffer中可以直接put和get String，可以直接将内容转成十六进制等等。<br>　　但是事实上，作者认为这个做法非常的糟糕。缓存只是缓存：一个用以保存在其被使用之前的临时数据的临时空间。有很多现有解决方案，比如定义一个依赖于NIO ByteBuffer列表的包装器，只需要把现有缓存拷贝到一个更大的缓存中区，因为我们只是需要扩展缓存的容量。<br>　　也可能使用 InputStream 取代贯穿一系列过滤器字节缓存更合适，因为它并不包含任何存储的数据的性质：可以是一个字节数组、字符串、消息…最后，当前实现违背了一个目标：零拷贝策略(比如，一旦从套接字中读取了数据，如果想在稍后的过程中避免再次拷贝)。既然我们使用可扩展字节缓存，如果我们需要管理大的消息我们就必须得拷贝这些数据。假定 MINA ByteBuffer 只是 NIO ByteBuffer 之上的一个包装者，这在使用直接缓存的时候就是一个真正的问题了。</p>
<h2 id="u53C2_u8003_u94FE_u63A5"><a href="#u53C2_u8003_u94FE_u63A5" class="headerlink" title="参考链接"></a>参考链接</h2><p><a href="http://my.oschina.net/ielts0909/blog/92035" target="_blank" rel="external">http://my.oschina.net/ielts0909/blog/92035</a><br><a href="http://my.oschina.net/ielts0909/blog?catalog=201987" target="_blank" rel="external">http://my.oschina.net/ielts0909/blog?catalog=201987</a></p>

      
    </div>
    <footer class="article-footer">
      <a data-url="http://yoursite.com/2016/01/21/nio/19.Mina系列五之IoSession/" data-id="cik00io9v0000mgfshtvo752a" class="article-share-link">Share</a>
      
      
  <ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/mina/">mina</a></li><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/nio/">nio</a></li></ul>

    </footer>
  </div>
  
</article>


  
    <article id="post-spring/3.spring基于Schema的自定义配置" class="article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    <a href="/2016/01/21/spring/3.spring基于Schema的自定义配置/" class="article-date">
  <time datetime="2016-01-21T07:16:00.000Z" itemprop="datePublished">2016-01-21</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/categories/spring/">spring</a>
  </div>

  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 itemprop="name">
      <a class="article-title" href="/2016/01/21/spring/3.spring基于Schema的自定义配置/">spring基于Schema的自定义配置</a>
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <h2 id="u7B80_u4ECB"><a href="#u7B80_u4ECB" class="headerlink" title="简介"></a>简介</h2><p>　　spring提供的schma配置机制使得配置工作大大减少，不需要再写臃肿的xml代码。很多第三方工具，比如dubbo、commonrpc等产品基于spring schema提供了产品的统一配置方法，方便与spring快速集成！下面我们通过一个示例介绍如何实现该功能，方便为我们自己的产品提供标准的初始化入口！<br>　　如果想看schema扩展实际在项目中的应用，建议大家看看oschina上的开源项目commonrpc！dubbo在这块封装的不是太好，有点乱！</p>
<h2 id="u6B65_u9AA4"><a href="#u6B65_u9AA4" class="headerlink" title="步骤"></a>步骤</h2><p>　　完成一个自定义配置一般需要以下步骤：<br>　　1. 设计配置属性和JavaBean<br>　　2. 编写XSD文件<br>　　3. 编写NamespaceHandler和BeanDefinitionParser完成解析工作<br>　　4. 编写spring.handlers和spring.schemas串联起所有部件<br>　　5. 在Bean文件中应用 </p>
<h2 id="u8BBE_u8BA1_u914D_u7F6E_u5C5E_u6027_u548CJavaBean"><a href="#u8BBE_u8BA1_u914D_u7F6E_u5C5E_u6027_u548CJavaBean" class="headerlink" title="设计配置属性和JavaBean"></a>设计配置属性和JavaBean</h2><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">People</span> </span>&#123;</span><br><span class="line">    <span class="keyword">private</span> String id;</span><br><span class="line">    <span class="keyword">private</span> String name;</span><br><span class="line">    <span class="keyword">private</span> Integer age;</span><br><span class="line">	<span class="comment">//getters and setters</span></span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
<p>　　注意：id是默认需要的</p>
<h2 id="u7F16_u5199XSD_u6587_u4EF6"><a href="#u7F16_u5199XSD_u6587_u4EF6" class="headerlink" title="编写XSD文件"></a>编写XSD文件</h2><p>　　为上一步设计好的配置项编写XSD文件，XSD是schema的定义文件，配置的输入和解析输出都是以XSD为契约，本例中XSD如下：<br><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><span class="line"><span class="pi">&lt;?xml version="1.0" encoding="UTF-8"?&gt;</span></span><br><span class="line"><span class="tag">&lt;<span class="title">xsd:schema</span></span><br><span class="line">        <span class="attribute">xmlns</span>=<span class="value">"http://study.shc.com/spring/schema/people"</span></span><br><span class="line">        <span class="attribute">xmlns:xsd</span>=<span class="value">"http://www.w3.org/2001/XMLSchema"</span></span><br><span class="line">        <span class="attribute">xmlns:beans</span>=<span class="value">"http://www.springframework.org/schema/beans"</span></span><br><span class="line">        <span class="attribute">targetNamespace</span>=<span class="value">"http://study.shc.com/spring/schema/people"</span></span><br><span class="line">        <span class="attribute">elementFormDefault</span>=<span class="value">"qualified"</span></span><br><span class="line">        <span class="attribute">attributeFormDefault</span>=<span class="value">"unqualified"</span>&gt;</span></span><br><span class="line"></span><br><span class="line">    <span class="tag">&lt;<span class="title">xsd:import</span> <span class="attribute">namespace</span>=<span class="value">"http://www.springframework.org/schema/beans"</span> /&gt;</span></span><br><span class="line">    <span class="tag">&lt;<span class="title">xsd:element</span> <span class="attribute">name</span>=<span class="value">"people"</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="title">xsd:complexType</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="title">xsd:complexContent</span>&gt;</span></span><br><span class="line">                <span class="tag">&lt;<span class="title">xsd:extension</span> <span class="attribute">base</span>=<span class="value">"beans:identifiedType"</span>&gt;</span></span><br><span class="line">                    <span class="tag">&lt;<span class="title">xsd:attribute</span> <span class="attribute">name</span>=<span class="value">"name"</span> <span class="attribute">type</span>=<span class="value">"xsd:string"</span> /&gt;</span></span><br><span class="line">                    <span class="tag">&lt;<span class="title">xsd:attribute</span> <span class="attribute">name</span>=<span class="value">"age"</span> <span class="attribute">type</span>=<span class="value">"xsd:int"</span> /&gt;</span></span><br><span class="line">                <span class="tag">&lt;/<span class="title">xsd:extension</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;/<span class="title">xsd:complexContent</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;/<span class="title">xsd:complexType</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;/<span class="title">xsd:element</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;/<span class="title">xsd:schema</span>&gt;</span></span><br></pre></td></tr></table></figure></p>
<p>　　注意：xmlns和targetNamespace的值需要保持一致，关于xsd:schema的各个属性具体含义可以参见<a href="http://www.w3school.com.cn/schema/schema_schema.asp" target="_blank" rel="external">http://www.w3school.com.cn/schema/schema_schema.asp</a>。完成后把该xsd文件存放在META-INF目录下。</p>
<h2 id="u7F16_u5199NamespaceHandler_u548CBeanDefinitionParser"><a href="#u7F16_u5199NamespaceHandler_u548CBeanDefinitionParser" class="headerlink" title="编写NamespaceHandler和BeanDefinitionParser"></a>编写NamespaceHandler和BeanDefinitionParser</h2><p>　　NamespaceHandler会根据schema和节点名找到某个BeanDefinitionParser，然后由BeanDefinitionParser完成具体的解析工作。因此需要分别完成NamespaceHandler和BeanDefinitionParser的实现类，Spring提供了默认实现类NamespaceHandlerSupport和AbstractSingleBeanDefinitionParser，简单的方式就是去继承这两个类<br><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">MyNamespaceHandler</span> <span class="keyword">extends</span> <span class="title">NamespaceHandlerSupport</span> </span>&#123;</span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">init</span><span class="params">()</span> </span>&#123;</span><br><span class="line">        <span class="comment">//此处可以注册多个不同的类</span></span><br><span class="line">        <span class="comment">//把节点名和解析类联系起来</span></span><br><span class="line">        registerBeanDefinitionParser(<span class="string">"people"</span>, <span class="keyword">new</span> PeopleBeanDefinitionParser());</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br><span class="line"></span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">PeopleBeanDefinitionParser</span> <span class="keyword">extends</span> <span class="title">AbstractSingleBeanDefinitionParser</span> </span>&#123;</span><br><span class="line">    <span class="function"><span class="keyword">protected</span> Class <span class="title">getBeanClass</span><span class="params">(Element element)</span> </span>&#123;</span><br><span class="line">        <span class="keyword">return</span> People.class;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="annotation">@Override</span></span><br><span class="line">    <span class="function"><span class="keyword">protected</span> <span class="keyword">void</span> <span class="title">doParse</span><span class="params">(Element element, BeanDefinitionBuilder bean)</span> </span>&#123;</span><br><span class="line">        String name = element.getAttribute(<span class="string">"name"</span>);</span><br><span class="line">        String age = element.getAttribute(<span class="string">"age"</span>);</span><br><span class="line">        String id = element.getAttribute(<span class="string">"id"</span>);</span><br><span class="line">        <span class="keyword">if</span> (StringUtils.hasText(id)) &#123;</span><br><span class="line">            <span class="comment">//用配置中的属性值放到bean中</span></span><br><span class="line">            bean.addPropertyValue(<span class="string">"id"</span>, id);</span><br><span class="line">        &#125;</span><br><span class="line">        <span class="keyword">if</span> (StringUtils.hasText(name)) &#123;</span><br><span class="line">            bean.addPropertyValue(<span class="string">"name"</span>, name);</span><br><span class="line">        &#125;</span><br><span class="line">        <span class="keyword">if</span> (StringUtils.hasText(age)) &#123;</span><br><span class="line">            bean.addPropertyValue(<span class="string">"age"</span>, Integer.valueOf(age));</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure></p>
<h2 id="u7F16_u5199spring-handlers_u548Cspring-schemas"><a href="#u7F16_u5199spring-handlers_u548Cspring-schemas" class="headerlink" title="编写spring.handlers和spring.schemas"></a>编写spring.handlers和spring.schemas</h2><p>　　spring.handlers和spring.schemas配置文件负责将开发好的handler与xsd进行关联。这两个文件需要我们自己编写并放入META-INF文件夹中，这两个文件的地址必须是META-INF/spring.handlers和META-INF/spring.schemas，spring会默认去载入它们，文件内容如下所示：<br><figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">#spring.schemas文件内容</span><br><span class="line">http\://study.shc.com/spring/schema/people.xsd=META-INF/people.xsd</span><br><span class="line"></span><br><span class="line">#spring.handlers文件内容</span><br><span class="line">#表示当使用到名为"http://study.shc.com/spring/schema/people"的schema引用时，会通过study.schemaExt.MyNamespaceHandler来完成解析</span><br><span class="line">http\://study.shc.com/spring/schema/people=com.shc.study.spring.schema.MyNamespaceHandler</span><br></pre></td></tr></table></figure></p>
<h2 id="u5728Bean_u6587_u4EF6_u4E2D_u5E94_u7528"><a href="#u5728Bean_u6587_u4EF6_u4E2D_u5E94_u7528" class="headerlink" title="在Bean文件中应用"></a>在Bean文件中应用</h2><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="tag">&lt;<span class="title">beans</span> <span class="attribute">xmlns</span>=<span class="value">"http://www.springframework.org/schema/beans"</span></span><br><span class="line">    <span class="attribute">xmlns:xsi</span>=<span class="value">"http://www.w3.org/2001/XMLSchema-instance"</span></span><br><span class="line">    <span class="attribute">xmlns:myreource</span>=<span class="value">"http://study.shc.com/spring/schema/people"</span></span><br><span class="line">    <span class="attribute">xsi:schemaLocation</span>=<span class="value">"</span><br><span class="line">        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd</span><br><span class="line">        http://study.shc.com/spring/schema/people http://study.shc.com/spring/schema/people.xsd"</span>&gt;</span></span><br><span class="line">    </span><br><span class="line">    <span class="tag">&lt;<span class="title">myreource:people</span> <span class="attribute">id</span>=<span class="value">"peopleTest"</span> <span class="attribute">name</span>=<span class="value">"shihc"</span> <span class="attribute">age</span>=<span class="value">"27"</span>/&gt;</span></span><br><span class="line"></span><br><span class="line"><span class="tag">&lt;/<span class="title">beans</span>&gt;</span></span><br></pre></td></tr></table></figure>
<h2 id="u6D4B_u8BD5_u4EE3_u7801"><a href="#u6D4B_u8BD5_u4EE3_u7801" class="headerlink" title="测试代码"></a>测试代码</h2><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">Test</span> </span>&#123;</span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">final</span> <span class="keyword">void</span> <span class="title">main</span><span class="params">(String[] args)</span></span>&#123;</span><br><span class="line">        ApplicationContext ctx = <span class="keyword">new</span> ClassPathXmlApplicationContext(<span class="string">"spring-config.xml"</span>);</span><br><span class="line">        People p = (People)ctx.getBean(<span class="string">"peopleTest"</span>);</span><br><span class="line">        System.out.println(p.getId());</span><br><span class="line">        System.out.println(p.getName());</span><br><span class="line">        System.out.println(p.getAge());</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
<p>#总结<br>　　这里我们就完成了所有的代码，源码下载地址：<a href="/code/spring-schema-demo.zip">spring-schema-demo.zip</a></p>
<h2 id="u53C2_u8003_u8D44_u6599"><a href="#u53C2_u8003_u8D44_u6599" class="headerlink" title="参考资料"></a>参考资料</h2><p><a href="http://www.cnblogs.com/jifeng/archive/2011/09/14/2176599.html" target="_blank" rel="external">http://www.cnblogs.com/jifeng/archive/2011/09/14/2176599.html</a></p>

      
    </div>
    <footer class="article-footer">
      <a data-url="http://yoursite.com/2016/01/21/spring/3.spring基于Schema的自定义配置/" data-id="cijzzpv26001f1cfsnjticq31" class="article-share-link">Share</a>
      
      
  <ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/java/">java</a></li><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/spring/">spring</a></li></ul>

    </footer>
  </div>
  
</article>


  
  
    <nav id="page-nav">
      <a class="extend prev" rel="prev" href="/">&laquo; Prev</a><a class="page-number" href="/">1</a><span class="page-number current">2</span><a class="page-number" href="/page/3/">3</a><a class="page-number" href="/page/4/">4</a><span class="space">&hellip;</span><a class="page-number" href="/page/15/">15</a><a class="extend next" rel="next" href="/page/3/">Next &raquo;</a>
    </nav>
  
</section>
        
          <aside id="sidebar">
  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Categories</h3>
    <div class="widget">
      <ul class="category-list"><li class="category-list-item"><a class="category-list-link" href="/categories/cache/">cache</a><span class="category-list-count">3</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/database/">database</a><span class="category-list-count">13</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/docker/">docker</a><span class="category-list-count">6</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/gis/">gis</a><span class="category-list-count">4</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/git/">git</a><span class="category-list-count">2</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/go/">go</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/gradle/">gradle</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/groovy/">groovy</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/hadoop/">hadoop</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/java/">java</a><span class="category-list-count">18</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/linux/">linux</a><span class="category-list-count">4</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/log/">log</a><span class="category-list-count">11</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/mongodb/">mongodb</a><span class="category-list-count">14</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/mq/">mq</a><span class="category-list-count">6</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/mybatis/">mybatis</a><span class="category-list-count">9</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/netty/">netty</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/nginx/">nginx</a><span class="category-list-count">3</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/nio/">nio</a><span class="category-list-count">19</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/nodejs/">nodejs</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/redis/">redis</a><span class="category-list-count">6</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/rpc/">rpc</a><span class="category-list-count">14</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/spring/">spring</a><span class="category-list-count">5</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/tomcat/">tomcat</a><span class="category-list-count">2</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/zookeeper/">zookeeper</a><span class="category-list-count">2</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/杂项/">杂项</a><span class="category-list-count">2</span></li></ul>
    </div>
  </div>

  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Tags</h3>
    <div class="widget">
      <ul class="tag-list"><li class="tag-list-item"><a class="tag-list-link" href="/tags/aop/">aop</a><span class="tag-list-count">8</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/aspectj/">aspectj</a><span class="tag-list-count">3</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/cache/">cache</a><span class="tag-list-count">3</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/classloader/">classloader</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/common-loggings/">common-loggings</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/commonjs/">commonjs</a><span class="tag-list-count">4</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/database/">database</a><span class="tag-list-count">10</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/docker/">docker</a><span class="tag-list-count">6</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/dubbo/">dubbo</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/fastdfs/">fastdfs</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/gclib/">gclib</a><span class="tag-list-count">2</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/geohash/">geohash</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/gis/">gis</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/git/">git</a><span class="tag-list-count">2</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/go/">go</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/gradle/">gradle</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/groovy/">groovy</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/hadoop/">hadoop</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/hessisan/">hessisan</a><span class="tag-list-count">3</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/java/">java</a><span class="tag-list-count">48</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/javassist/">javassist</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/kafka/">kafka</a><span class="tag-list-count">3</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/linux/">linux</a><span class="tag-list-count">4</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/log/">log</a><span class="tag-list-count">11</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/log4j/">log4j</a><span class="tag-list-count">4</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/logback/">logback</a><span class="tag-list-count">5</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/map/">map</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/memecached/">memecached</a><span class="tag-list-count">2</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/mina/">mina</a><span class="tag-list-count">5</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/mongodb/">mongodb</a><span class="tag-list-count">14</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/mq/">mq</a><span class="tag-list-count">6</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/mqtt/">mqtt</a><span class="tag-list-count">2</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/mybatis/">mybatis</a><span class="tag-list-count">9</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/netty/">netty</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/nginx/">nginx</a><span class="tag-list-count">3</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/nio/">nio</a><span class="tag-list-count">5</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/nodejs/">nodejs</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/postgresql/">postgresql</a><span class="tag-list-count">2</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/protobuf/">protobuf</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/proxy/">proxy</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/push/">push</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/rabbitmq/">rabbitmq</a><span class="tag-list-count">3</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/redis/">redis</a><span class="tag-list-count">6</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/rpc/">rpc</a><span class="tag-list-count">14</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/sitemesh/">sitemesh</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/sld/">sld</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/slf4j/">slf4j</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/socket/">socket</a><span class="tag-list-count">7</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/spatialite-for-android/">spatialite for android</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/spi/">spi</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/spring/">spring</a><span class="tag-list-count">13</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/tomcat/">tomcat</a><span class="tag-list-count">2</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/transaction/">transaction</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/web/">web</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/xSocket/">xSocket</a><span class="tag-list-count">7</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/zbus/">zbus</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/zookeeper/">zookeeper</a><span class="tag-list-count">2</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/数据结构/">数据结构</a><span class="tag-list-count">2</span></li></ul>
    </div>
  </div>

  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Tag Cloud</h3>
    <div class="widget tagcloud">
      <a href="/tags/aop/" style="font-size: 15.38px;">aop</a> <a href="/tags/aspectj/" style="font-size: 11.54px;">aspectj</a> <a href="/tags/cache/" style="font-size: 11.54px;">cache</a> <a href="/tags/classloader/" style="font-size: 10px;">classloader</a> <a href="/tags/common-loggings/" style="font-size: 10px;">common-loggings</a> <a href="/tags/commonjs/" style="font-size: 12.31px;">commonjs</a> <a href="/tags/database/" style="font-size: 16.92px;">database</a> <a href="/tags/docker/" style="font-size: 13.85px;">docker</a> <a href="/tags/dubbo/" style="font-size: 10px;">dubbo</a> <a href="/tags/fastdfs/" style="font-size: 10px;">fastdfs</a> <a href="/tags/gclib/" style="font-size: 10.77px;">gclib</a> <a href="/tags/geohash/" style="font-size: 10px;">geohash</a> <a href="/tags/gis/" style="font-size: 10px;">gis</a> <a href="/tags/git/" style="font-size: 10.77px;">git</a> <a href="/tags/go/" style="font-size: 10px;">go</a> <a href="/tags/gradle/" style="font-size: 10px;">gradle</a> <a href="/tags/groovy/" style="font-size: 10px;">groovy</a> <a href="/tags/hadoop/" style="font-size: 10px;">hadoop</a> <a href="/tags/hessisan/" style="font-size: 11.54px;">hessisan</a> <a href="/tags/java/" style="font-size: 20px;">java</a> <a href="/tags/javassist/" style="font-size: 10px;">javassist</a> <a href="/tags/kafka/" style="font-size: 11.54px;">kafka</a> <a href="/tags/linux/" style="font-size: 12.31px;">linux</a> <a href="/tags/log/" style="font-size: 17.69px;">log</a> <a href="/tags/log4j/" style="font-size: 12.31px;">log4j</a> <a href="/tags/logback/" style="font-size: 13.08px;">logback</a> <a href="/tags/map/" style="font-size: 10px;">map</a> <a href="/tags/memecached/" style="font-size: 10.77px;">memecached</a> <a href="/tags/mina/" style="font-size: 13.08px;">mina</a> <a href="/tags/mongodb/" style="font-size: 19.23px;">mongodb</a> <a href="/tags/mq/" style="font-size: 13.85px;">mq</a> <a href="/tags/mqtt/" style="font-size: 10.77px;">mqtt</a> <a href="/tags/mybatis/" style="font-size: 16.15px;">mybatis</a> <a href="/tags/netty/" style="font-size: 10px;">netty</a> <a href="/tags/nginx/" style="font-size: 11.54px;">nginx</a> <a href="/tags/nio/" style="font-size: 13.08px;">nio</a> <a href="/tags/nodejs/" style="font-size: 10px;">nodejs</a> <a href="/tags/postgresql/" style="font-size: 10.77px;">postgresql</a> <a href="/tags/protobuf/" style="font-size: 10px;">protobuf</a> <a href="/tags/proxy/" style="font-size: 10px;">proxy</a> <a href="/tags/push/" style="font-size: 10px;">push</a> <a href="/tags/rabbitmq/" style="font-size: 11.54px;">rabbitmq</a> <a href="/tags/redis/" style="font-size: 13.85px;">redis</a> <a href="/tags/rpc/" style="font-size: 19.23px;">rpc</a> <a href="/tags/sitemesh/" style="font-size: 10px;">sitemesh</a> <a href="/tags/sld/" style="font-size: 10px;">sld</a> <a href="/tags/slf4j/" style="font-size: 10px;">slf4j</a> <a href="/tags/socket/" style="font-size: 14.62px;">socket</a> <a href="/tags/spatialite-for-android/" style="font-size: 10px;">spatialite for android</a> <a href="/tags/spi/" style="font-size: 10px;">spi</a> <a href="/tags/spring/" style="font-size: 18.46px;">spring</a> <a href="/tags/tomcat/" style="font-size: 10.77px;">tomcat</a> <a href="/tags/transaction/" style="font-size: 10px;">transaction</a> <a href="/tags/web/" style="font-size: 10px;">web</a> <a href="/tags/xSocket/" style="font-size: 14.62px;">xSocket</a> <a href="/tags/zbus/" style="font-size: 10px;">zbus</a> <a href="/tags/zookeeper/" style="font-size: 10.77px;">zookeeper</a> <a href="/tags/数据结构/" style="font-size: 10.77px;">数据结构</a>
    </div>
  </div>

  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Archives</h3>
    <div class="widget">
      <ul class="archive-list"><li class="archive-list-item"><a class="archive-list-link" href="/archives/2016/01/">January 2016</a><span class="archive-list-count">67</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2015/12/">December 2015</a><span class="archive-list-count">81</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2015/11/">November 2015</a><span class="archive-list-count">2</span></li></ul>
    </div>
  </div>

  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Recents</h3>
    <div class="widget">
      <ul>
        
          <li>
            <a href="/2016/01/30/资料/">(no title)</a>
          </li>
        
          <li>
            <a href="/2016/01/30/java/13.Classloader系列一/">ClassLoader系列一</a>
          </li>
        
          <li>
            <a href="/2016/01/29/hadoop/1.hadoop系列之源码编译/">hadoop系列一之源码编译</a>
          </li>
        
          <li>
            <a href="/2016/01/29/go/1.GO系列之入门/">Go系列一之入门</a>
          </li>
        
          <li>
            <a href="/2016/01/28/tomcat/2.tomcat性能优化/">tomcat性能优化</a>
          </li>
        
      </ul>
    </div>
  </div>

  
</aside>
        
      </div>
      <footer id="footer">
  
  <div class="outer">
    <div id="footer-info" class="inner">
      &copy; 2016 John Doe<br>
      Powered by <a href="http://hexo.io/" target="_blank">Hexo</a>
    </div>
  </div>
</footer>
    </div>
    <nav id="mobile-nav">
  
    <a href="/" class="mobile-nav-link">Home</a>
  
    <a href="/archives" class="mobile-nav-link">Archives</a>
  
</nav>
    


<script src="http://libs.baidu.com/jquery/2.0.3/jquery.min.js"></script>


  <link rel="stylesheet" href="/fancybox/jquery.fancybox.css" type="text/css">
  <script src="/fancybox/jquery.fancybox.pack.js" type="text/javascript"></script>


<script src="/js/script.js" type="text/javascript"></script>

  </div>
</body>
</html>